![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsISPrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdsATOfd3bkFGazxCMx8VesATMfhHLlN3XnxCMwEzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5CNkhjYwUWO4YmYllTN4QGOkRmNwIGNiNTNwMmM3IzMj9CX1AzLchDM5EDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLxM3Lc9CX6MHc0RHaiojIsJye.png)
环境:
- 192.168.205.17: as master server
- 192.168.205.27: as middle server
- 192.168.205.37: as slave server
版本:
- OS: centos 7 1810 with mini install
- mariadb-5.5.60
目地:
如果在生产环境中可能有一个主服务器,也是读写服务器,并可能会有多个从服务器,但这个主服务器会随之从服务器的增加而变的性能下降,所以最好主服务器只有一个从,其它的从服务器再从这人从服务器进行复制,减少主服务器的压力,这样中间服务器专做复制从而最小化影响主服务器的性能
步骤:
- 配置主服务器
- 备份中间服务器
- 配置从服务器
- 测试
配置主服务器
- 安装三台服务器
[[email protected] ~]#yum install mariadb-server [[email protected] ~]#mkdir /data/{mysql,logs} [[email protected] ~]#chown mysql:mysql /data/{mysql,logs}
- 修改主服务器的配置文件
[[email protected] ~]#vi /etc/my.cnf [mysqld] log-bin=/data/logs/bin datadir=/data/mysql server-id=17
- 重新启动服务
[[email protected] ~]#systemctl start mariadb
- 加载一个测试数据库
[[email protected] ~]#mysql < hellodb_innodb.sql
- 创建复帽帐号
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos';
- 备份数据库并拷贝到中间节点中
[[email protected] ~]#mysqldump -A --single-transaction --master-data=1 > /data/all.sql [[email protected] ~]#scp /data/all.sql 192.168.205.27:/data
配置中间级联服务器
- 设置中间服务器
[[email protected] ~]#vi /etc/my.cnf [mysqld] datadir=/data/mysql log-bin=/data/logs/bin server-id=27 read-only log_slave_updates
- 修改备份的数据
[[email protected] ~]#vi /data/all.sql CHANGE MASTER TO MASTER_HOST='192.168.205.17', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306,
- 导入数据
MariaDB [(none)]> source /data/all.sql
- 启动线程
MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.17 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000003 Read_Master_Log_Pos: 7973 Relay_Log_File: mariadb-relay-bin.000004 Relay_Log_Pos: 604 Relay_Master_Log_File: bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes
- 备份中间服务器的数据,并拷到从服务器上
[[email protected] ~]#mysqldump -A --single-transaction --master-data=1 > /data/middle.sql [[email protected] ~]#scp /data/middle.sql 192.168.205.37:/data
修改从服务器
- 修改从服务器配置文件
[[email protected] ~]#vi /etc/my.cnf [mysqld] datadir=/data/mysql server-id=37 read_only
- 修改备份的文件middle.sql
[[email protected] ~]#vi /data/middle.sql CHANGE MASTER TO MASTER_HOST='192.168.205.27', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306,
- 导入数据并启动服务
[[email protected] ~]#mysql < /data/middle.sql [[email protected] ~]#systemctl start mariadb
- 启动线程
MariaDB [(none)]> start salve; MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.27 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000005 Read_Master_Log_Pos: 326 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 604 Relay_Master_Log_File: bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes
测试
- 测试在主服务器上建库
MariaDB [(none)]> create database zhaoli;
Query OK, 1 row affected (0.00 sec)
- 在中间和从节点分别查看数据库,同步成功
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | hellodb | | mysql | | performance_schema | | test | | zhaoli | +--------------------+ 8 rows in set (0.00 sec)