Mysql主从同步+mysql-proxy读写分离

Mysql主从同步+mysql-proxy读写分离

如果对运维课程感兴趣，可以在b站上、csdn或微信视频号 上搜索我的账号： 运维实战课程，可以关注我，学习更多免费的运维实战技术视频

一、配置mysql服务器的主从结构 （开始时主从数据不一致时候需要找到临界点的主从同步）

结果使得：在主mysql服务器上创建一个库和表并赋值，则在从mysql服务器上也同时把数据同步过来。而如果在从mysql建一个库和表并赋值，在主mysql上不会自动把数据同步过来。

在192.168.4.5（主）上：

[root@localhost 桌面]# yum -y install mysql-server mysql

[root@localhost 桌面]# /etc/init.d/mysqld start

[root@localhost 桌面]# mysql

mysql> set password for root@"localhost"=password("123");

mysql> quit

[root@localhost 桌面]# mysql -uroot -p123           //主从同步前，建立初始数据（同步前数据不一致建立）

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| test               |

+--------------------+

mysql> create database ku;

mysql> create table ku.biao (test varchar(5));

mysql> insert into ku.biao values ("test");

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ku                 |

| mysql              |

| test               |

+--------------------+

mysql> show tables from ku;

+--------------+

| Tables_in_ku |

+--------------+

| biao         |

+--------------+

1 row in set (0.00 sec)

mysql> select * from ku.biao;

+------+

| test |

+------+

| test |

+------+

mysql> quit

[root@localhost 桌面]# vim /etc/my.cnf

server_id=5                                 //主从编号不能一致

log-bin                                       //开启bin-log

wq

[root@localhost 桌面]# mysql -uroot -p123 -e "show variables like 'log_bin'"

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | OFF   |                              //还没重启服务，配置文件没生效，重启后会开启

+---------------+-------+

[root@localhost 桌面]# ls /var/lib/mysql/

ibdata1  ib_logfile0  ib_logfile1  ku  mysql  mysql.sock  test             //没重启时没有bin-log日志

[root@localhost 桌面]# /etc/init.d/mysqld restart

[root@localhost 桌面]# mysql -uroot -p123 -e "show variables like 'log_bin'"

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

[root@localhost 桌面]# ls /var/lib/mysql/

ibdata1      ib_logfile1  mysql              mysqld-bin.index  test

ib_logfile0  ku           mysqld-bin.000001  mysql.sock

[root@localhost 桌面]# mysql -uroot -p123

mysql> grant replication slave on *.* to shi@"192.168.4.205" identified by "456";

mysql> flush privileges;

mysql> flush tables with read lock;        //把主库的数据先锁上，此时窗口不能断，另开一窗口，备份数据

[root@localhost 桌面]# mysql -uroot -p123                          //拿到那个临界点

mysql> show master status;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| mysqld-bin.000001 |      329 |              |                  |

+-------------------+----------+--------------+------------------+

mysql> show master logs;

+-------------------+-----------+

| Log_name          | File_size |

+-------------------+-----------+

| mysqld-bin.000001 |       329 |

+-------------------+-----------+

mysql> quit

                                            //下面的：-A，备份所有，-B,有create语句

[root@localhost 桌面]# mysqldump -uroot -p123 -A -B --events --master-data=2 > /tmp/mysql.bak.sql

                                       //也可加-X,自动锁，就不需上面专门锁了，--master-data=2，取临界点

[root@localhost 桌面]# ls /tmp/

mysql.bak.sql

[root@localhost 桌面]# scp /tmp/mysql.bak.sql root@192.168.4.205:/tmp/

在192.168.4.205（从）上：

[root@localhost 桌面]# yum -y install mysql-server mysql

[root@localhost 桌面]# vim /etc/my.cnf

log-bin                                             //单纯做从，也可不开启，如果做主必须开启

server_id=205

[root@localhost 桌面]# /etc/init.d/mysqld start

[root@localhost 桌面]# mysql

mysql> set password for root@"localhost"=password("123");

mysql> quit

[root@localhost 桌面]# mysql -uroot -p123

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| test               |