介绍:
主从级联是为了减轻主数据库的压力,这样更有利于实现负载均衡。如果我们没有级联服务器,虽然也能实现主从同步的效果,但是我们不建议用,一方面是主数据库的压力过大,另一方面是效率过低。当我们有级联时,我们的主服务器,只需要将自己的数据同步给级联即可,其余的从服务器由级联服务器去同步,这样就能提高效率和减轻主数据库的压力了。
ip配置如下:
master(主)服务器:172.18.254.223
cascade(级联)服务器:172.18.254.227
slave1(从)服务器:172.18.254.228
实现思路:
先将cascade当做slave来对待,然后让master与之同步,切记:cascade是有用户的(master主从同步创建的目录),然后将cascade当做主(master)来对slave1作主从同步。
master的配置文件:
vim /etc/my.cnf
server_id=1 #区分id没什么意义,但是必须有,且都不同
datadir=/mysql/data #指定数据文件的存放位置
log_bin=/mysql/logbin/log #指定二进制文件的存放位置
innodb_file_per_table #将二进制文件分开存放
cascade配置文件:
vim /etc/my.cnf
server_id=2 #区分id没什么意义,但是必须有,且都不同
innodb_file_per_table
datadir=/mysql/data
log_bin=/mysql/logbin/log #和下面一起的
log_slave_updates #这个必须写上,因为cascade是有二进制文件的
slave1配置文件:
vim /etc/my.cnf
server_id=3 #区分id没什么意义,但是必须有,且都不同
datadir=/mysql/data
log_bin=/mysql/logbin/log
innodb_file_per_table
master的数据库显示:
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| log.000004 | 245 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to zhang@'172.18.254.%' identified by 'centos';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> select user from mysql.user;
+-------+
| user |
+-------+
| root |
| zhang |
| root |
| |
| root |
| |
| root |
+-------+
7 rows in set (0.00 sec)
cascade数据库的显示:
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.254.223',
-> MASTER_USER='zhang',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=‘log.000004',
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.07 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status;
+------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| log.000003 | 478 | | |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)
slave1数据库显示:
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.254.227',
-> MASTER_USER='zhang',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=‘log.000003',
-> MASTER_LOG_POS=478,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.07 sec)
MariaDB [(none)]> start slave;
补充:删除从的设置:
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.17 sec)