前言
    MySQL 5.7.20测试主从复制
 
环境
    主库 192.168.1.59  t-xi-sonar01
    从库 192.168.1.51  t-xi-orc01
 
设定主机host文件

  

主库
[root@t-xi-sonar01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.59 t-xi-sonar01
192.168.1.51 t-xi-orc01
从库
[root@t-xi-orc01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.51 t-xi-orc01
192.168.1.59 t-xi-sonar01
 
Mysql数据库配置
    【Master-Server】
 

[root@t-xi-sonar01 ~]# service mysqld stop
 Stopping mysqld:                                           [  OK  ]
[root@t-xi-sonar01 ~]# vim /etc/my.cnf
#Server ID,一般设置成IP地址的最后一位,如下测试就按后两位
server_id=59
#开启log bin,名字最好有意义用来区分
log-bin=dev-bin
#需要进行复制的数据库,可以指定数据库
#binlog-do-db=DB_master
#不需要备份的数据库,可以设置多个数据库,一般不会同步mysql这个库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1m
#二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
# 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
[root@t-xi-sonar01 ~]# service mysqld start
Starting mysqld:                                           [  OK  ]
[root@t-xi-sonar01 ~]#
 
mysql 5.7.20登陆报错解决
  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
mysql -u root
update user set authentication_string=PASSWORD("****") where User='root';
flush privileges;
 
主库创建同步账户
service mysqld start
mysql> mysql -u root -p
mysql> CREATE USER 'replication'@'192.168.1.51' IDENTIFIED BY 'slave';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.51';
mysql> flush privileges;
 
主库锁定后备份将资料同步到从库
mysql>use sonar
mysql>FLUSH TABLES WITH READ LOCK;
mysqldump -u root -p --databases sonar > sonar.sql
scp sonar.sql @192.168.1.51:/root
mysql> unlock tables;
[master-server]
mysql> show master status ;
+----------------+----------+--------------+---------------------------------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+----------------+----------+--------------+---------------------------------------------+-------------------+
| dev-bin.000004 |      783 |              | mysql,information_schema,performance_schema |                   |
+----------------+----------+--------------+---------------------------------------------+-------------------+

 

    【Slave-Server】
 
       

 service mysqld stop
vim /etc/my.cnf
#add slave-server
server_id=51
#binlog-ignore-db=mydql
#binlog-ignore-db=information_schema
#binlog-ignore-db=performance_schema
#log-bin=dev-slave-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=dev-relay-bin
#log_slave_updates=1
read_only=1
 
service mysqld start
将主库备份导入从库
mysql>source /root/sonar.sql
添加链接到主库同步复制的账户
mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.59', MASTER_USER='replication', MASTER_PASSWORD='slave', MASTER_LOG_FILE='dev-bin.000001', MASTER_LOG_POS=0;
    
MASTER_LOG_FILE:指定log bin日志文件名称
MASTER_LOG_POS :指定同步复制log分区号,可以从0开始。
 
查看slave状态
show slave status \G
    
Slave_IO_State #从站的当前状态
Slave_IO_Running: Yes #读取主程序二进制日志的I/O线程是否正在运行
Slave_SQL_Running: Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样
Seconds_Behind_Master #是否为0,0就是已经同步了
启动slave
start slave;
mysql>     show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.59
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: dev-bin.000004
          Read_Master_Log_Pos: 1255
               Relay_Log_File: dev-relay-bin.000005
                Relay_Log_Pos: 1464
        Relay_Master_Log_File: dev-bin.000004
             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: 1255
              Relay_Log_Space: 1878
              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: 59
                  Master_UUID: d6901902-ea28-11e7-b859-000c29255261
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

 

    

 

 
    【Master-Server】
        

创建table和database测试
     
在sonar下建立测试表
mysql> use sonar;   
mysql> create table slave_t(
    -> id int(10) not null, name varchar(20)
    -> )
    -> ;
Query OK, 0 rows affected (1.57 sec)
 
mysql> insert into slave_t values(1,'name01');
Query OK, 1 row affected (0.33 sec)
  
创建slave_db测试数据库
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> create database slave_db;
Query OK, 1 row affected (0.17 sec)

 

 
    【Slave-Server】
    

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| slave_db           |
| sonar              |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
 
mysql> use sonar;
Database changed
mysql> select * from slave_t;
+----+--------+
| id | name   |
+----+--------+
|  1 | name01 |
+----+--------+
1 row in set (0.00 sec)

主库上的table和database已同步复制过来

 

 

 
【命令参考】
查看主库master状态
mysql> show master status;
+----------------+----------+--------------+---------------------------------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+----------------+----------+--------------+---------------------------------------------+-------------------+
| dev-bin.000004 |     1426 |              | mysql,information_schema,performance_schema |                   |
+----------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)
 
查看从库主机列表
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|        51 |      | 3306 |        59 | 86fff1d0-f62d-11e7-834d-000c29477dac |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
 
查看bin log文件列表
mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| dev-bin.000001 |       177 |
| dev-bin.000002 |       177 |
| dev-bin.000003 |       177 |
| dev-bin.000004 |      1426 |
+----------------+-----------+
4 rows in set (0.00 sec)
 
查看bin log文件的内容
mysql> show binlog events;
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name       | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| dev-bin.000001 |   4 | Format_desc    |        59 |         123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| dev-bin.000001 | 123 | Previous_gtids |        59 |         154 |                                       |
| dev-bin.000001 | 154 | Stop           |        59 |         177 |                                       |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)
 
mysql> show binlog events in 'dev-bin.000004';
+----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+
| Log_name       | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                            |
+----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+
| dev-bin.000004 |    4 | Format_desc    |        59 |         123 | Server ver: 5.7.20-log, Binlog ver: 4                                                                                           |
| dev-bin.000004 |  123 | Previous_gtids |        59 |         154 |                                                                                                                                 |
| dev-bin.000004 |  154 | Anonymous_Gtid |        59 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            |
| dev-bin.000004 |  219 | Query          |        59 |         417 | CREATE USER 'replication'@'192.168.1.51' IDENTIFIED WITH 'mysql_native_password' AS '*51125B3597BEE0FC43E0BCBFEE002EF8641B44CF' |
| dev-bin.000004 |  417 | Anonymous_Gtid |        59 |         482 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            |
| dev-bin.000004 |  482 | Query          |        59 |         631 | GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.51'                                                                  |
| dev-bin.000004 |  631 | Anonymous_Gtid |        59 |         696 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            |
| dev-bin.000004 |  696 | Query          |        59 |         783 | flush privileges                                                                                                                |
| dev-bin.000004 |  783 | Anonymous_Gtid |        59 |         848 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            |
| dev-bin.000004 |  848 | Query          |        59 |         985 | use `sonar`; create table slave_t(
id int(10) not null, name varchar(20)
)                                                      |
| dev-bin.000004 |  985 | Anonymous_Gtid |        59 |        1050 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            |
| dev-bin.000004 | 1050 | Query          |        59 |        1123 | BEGIN                                                                                                                           |
| dev-bin.000004 | 1123 | Table_map      |        59 |        1177 | table_id: 326 (sonar.slave_t)                                                                                                   |
| dev-bin.000004 | 1177 | Write_rows     |        59 |        1224 | table_id: 326 flags: STMT_END_F                                                                                                 |
| dev-bin.000004 | 1224 | Xid            |        59 |        1255 | COMMIT /* xid=178006 */                                                                                                         |
| dev-bin.000004 | 1255 | Anonymous_Gtid |        59 |        1320 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            |
| dev-bin.000004 | 1320 | Query          |        59 |        1426 | create database slave_db                                                                                                        |
+----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+
17 rows in set (0.13 sec)
 
查看当前数据库线程列表
【master-server】
mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
     Id: 199
   User: root
   Host: localhost
     db: mysql
Command: Query
   Time: 0
  State: starting
   Info: SHOW PROCESSLIST
*************************** 2. row ***************************
     Id: 201
   User: replication
   Host: t-xi-orc01:41452
     db: NULL
Command: Binlog Dump
   Time: 1450
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 3. row ***************************
     Id: 203
   User: sonar
   Host: localhost:57162
     db: sonar
Command: Sleep
   Time: 434
  State:
   Info: NULL
*************************** 4. row ***************************
     Id: 204
   User: sonar
   Host: localhost:57358
     db: sonar
Command: Sleep
   Time: 123
  State:
   Info: NULL
*************************** 5. row ***************************
     Id: 205
   User: sonar
   Host: localhost:57524
     db: sonar
Command: Sleep
   Time: 2
  State:
   Info: NULL
*************************** 6. row ***************************
     Id: 206
   User: sonar
   Host: localhost:57720
     db: sonar
Command: Sleep
   Time: 3
  State:
   Info: NULL
6 rows in set (0.00 sec)
 
【slave-server】
mysql> SHOW PROCESSLIST  \G
*************************** 1. row ***************************
     Id: 4
   User: root
   Host: localhost
     db: sonar
Command: Query
   Time: 0
  State: starting
   Info: SHOW PROCESSLIST
*************************** 2. row ***************************
     Id: 5
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1445
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 6
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 26717
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
3 rows in set (0.00 sec)
 
 
 
从库启动复制
mysql> START SLAVE;
从库停止复制
mysql> STOP SLAVE;

 

 
参考: