数据库集群的级联复制完整实现
实验前的注意:
网站建设哪家好,找成都创新互联!专注于网页设计、网站建设、微信开发、微信平台小程序开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了凤泉免费建站欢迎大家使用!
如果要启用级联复制,需要在从服务器启用以下配置; [MySQLd] log_bin log_slave_updates log_slave_updates的作用: 因为默认从主服务器过来的二进制日志保存在从服务器的中继日志;并应用到本地数据库; 但是中间这个从服务器是即便是开启二进制日志的记录功能,他也是不记录到本地的二进制日志, 那么也就不能将对数据库的操作再传输到从的从服务器上去。当log_slave_updates启用后, 从服务器也就能将对主服务器的二进制日志在本地执行后,也记录到自己的二进制日志中; 删除数据库就可以干净做实验; [root@master ~]$systemctl stop mariadb [root@master ~]$rm -fr /var/lib/mysql/* [root@master ~]$systemctl start mariadb
主、从、二级从服务器的配置:
[root@master ~]$cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 innodb_file_per_table log_bin server-id=1 [root@slave ~]$cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. innodb_file_per_table server_id=2 read_only log_bin log_slave_updates [root@slave2 ~]$cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server_id=3
主服务器和从服务器实现主从复制,配置和授权:
主服务器: MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.27.%' identified by 'centos'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 264 | | mariadb-bin.000002 | 401 | +--------------------+-----------+ 2 rows in set (0.00 sec) 中间的从服务器: CHANGE MASTER TO MASTER_HOST='192.168.27.7', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245; MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.27.7', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=245; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.27.7 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 401 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 687 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 401 Relay_Log_Space: 1269 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 MariaDB [(none)]> select user from mysql.user; +----------+ | user | +----------+ | root | | repluser | | root | | | | root | | | | root | +----------+ 7 rows in set (0.00 sec) MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 401 | +--------------------+-----------+ 1 row in set (0.00 sec)
二级从服务器上的配置:
在从的从服务器上执行: [mysqld] innodb_file_per_table read-only server-id=3 CHANGE MASTER TO MASTER_HOST='192.168.27.17', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=401;
网站栏目:数据库集群的级联复制完整实现
本文网址:http://myzitong.com/article/peggdj.html