环境准备:
编号 | ip | 系统/MySQL版本 | 角色 |
1 | 192.168.1.120 | CentOS7.3/5.6 | master |
2 | 192.168.1.119 | CentOS7.3/5.6 | slave |
一、master节点
1、创建需要同步的数据库
mysql> create database test;
mysql> use test;
mysql> create table user(id int,name varchar(20));
2、停止数据库服务
systemctl stop mysqld.service
3、编辑my.cnf
[mysqld]
log-bin=mysql-bin #启用二进制日志
server-id=1 #本机数据库ID 标示
4、重启数据库服务
systemctl restart mysqld.service
5、创建同步用户并授权
mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%' identified by '123456';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 | 1881 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
7、将要同步的数据库导出,并发送给slave节点,复制前要保证同步的数据库一致
mysqldump -uroot -p123456 test >test.sql #可以导出数据库
scp test.sql 192.168.1.119:/root
二、slave节点
1.连接数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
3、正常导入数据库,和主数据库服务器保持一致
mysql> source /root/test.sql
4.从服务器没必要开启bin-log日志,修改从服务器配置文件
mysql> systemctl stop mysqld.service
mysql> vi /etc/my.cnf
[mysqld]
server-id=2 #从服务器ID号,不要和主ID相同 ,如果设置多个从服务器,
每个从服务器必须有一个唯一的server-id值,必须与主服务器的以及其它从
服务器的不相同。可以认为server-id值类似于IP地址:这些ID值能唯一识别
复制服务器群集中的每个服务器实例。
replicate-do-db=test //要同步的test数据库,要同步多个数据库,就多加几个replicate-db-db=数据库名
5、停止slave服务,设置master节点ip,master_user同步用户和密码
[root@localhost ~]# mysql -uroot -p
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.1.120',master_user='test',master_password='123456';
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.120
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000013
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes #负责与主机的io通信
Slave_SQL_Running: Yes #负责自己的slave mysql进程
Replicate_Do_DB: test
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: 120
Relay_Log_Space: 623
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
Master_UUID: 7c1e4b2a-0488-11ea-85c0-000c2979f017
Master_Info_File: /www/server/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
三、maste节点
mysql> show processlist\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: test
*************************** 1. row ***************************
Id: 2
User: test
Host: 192.168.1.109:52640
db: test
Command: Sleep
Time: 243
State:
Info: NULL
*************************** 2. row ***************************
Id: 3
User: test
Host: 192.168.1.119:38402 #已链接
db: NULL
Command: Binlog Dump
Time: 159
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 3. row ***************************
Id: 4
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: init
Info: show processlist
3 rows in set (0.00 sec)
(2)同步测试插入数据测试同步:
mysql> select * from user;
+----+--------+
| id | name |
+----+--------+
| 1 | 446454 |
+----+--------+
1 row in set (0.00 sec)
mysql> insert into user values(4,5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+--------+
| id | name |
+----+--------+
| 1 | 446454 |
| 4 | 5 |
+----+--------+
2 rows in set (0.00 sec)
四、slave节点验证数据是否同步
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)
mysql> select * from user;
+----+--------+
| id | name |
+----+--------+
| 1 | 446454 |
| 4 | 5 |
+----+--------+
2 rows in set (0.00 sec)