master节点操作
添加配置
vim /etc/my.cnf
在[mysqld]下添加一下参数
log-bin=mysql-bin #启动二进制日志系统 binlog-do-db=test #二进制需要同步的数据库名,如果需要同步多个库,例如要再同步westos库,再添加一行“binlog-do-db=westos”,以此类推 server-id=1 #必须为 1到 232–1之间的一个正整数值 binlog-ignore-db=mysql #禁止同步 mysql 数据库
重新启动数据库
systemctl restart mysqld
配置主服务器用户并清空日志
mysql> show privileges; mysql> grant replication client, replication slave on *.* to 'tongwz'@'192.168.1.%' identified by 'tongwz'; Query OK, 0 rows affected (0.00 sec) mysql> reset master; Query OK, 0 rows affected (0.02 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 107 | +------------------+-----------+ 1 row in set (0.00 sec)
slave节点操作
修改配置文件
cat /etc/my.cnf|grep server-id server-id = 1 sed -i 's+server-id = 1+server-id = 2+g' /etc/my.cnf cat /etc/my.cnf|grep server-id server-id = 2
重启数据库
systemctl restart mysqld
创建配置连接
mysql -uroot -p
输入密码：
mysql> change master to -> master_host='192.168.1.237', -> master_user='tongwz', -> master_password='tongwz', -> master_port=3306, -> master_log_file='mysql-bin.000001', -> master_log_pos=107; Query OK, 0 rows affected (0.01 sec)
此处的 master_log_file和master_log_pos与master中的数值对应
查看slave状态
先查看一下
mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.11 Master_User: larry Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 107 Relay_Log_File: serv08-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 107 Relay_Log_Space: 107 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) ERROR: No query specified
正式开启slave
mysql> start slave; Query OK, 0 rows affected (0.01 sec)
从服务器查看是否和主服务器通信成功。如果出现 Slave_IO_Running和Slave_SQL_Running都是yes，则证明配置成功
mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.11 Master_User: larry Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 107 Relay_Log_File: serv08-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 107 Relay_Log_Space: 410 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 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) ERROR: No query specified
此时，slave已经配置好，咱进一步查看
cd /usr/local/mysql/data cat relay-log.info ... 253 mysql-bin.000001 107 [root@serv08 data]# cat master.info 18 mysql-bin.000001 107 192.168.1.237 tongwz tongwz 3306
测试：
此次只同步了test数据库
在master节点操作test数据库下创建表
mysql> use test; Database changed mysql> create table test(id int(11)); Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(1); Query OK, 1 row affected (0.00 sec) mysql> show tables; +-------------------+ | Tables_in_larrydb | +-------------------+ | test | +-------------------+ 1 row in set (0.00 sec)
在slave节点查看
mysql> use test; Database changed mysql> show tables; +-------------------+ | Tables_in_larrydb | +-------------------+ | test | +-------------------+ 1 row in set (0.00 sec)
