基于传统复制模式下的主从同步搭建测试

1.基本信息

主库：
 IP：10.16.24.107  port:3376
 server-id = 1073376
 data_dir:/data/mysql/mysql3376/data/
 base_dir:/usr/local/mysql

从库：
 IP：10.16.24.108  port:3376
 server-id = 1083376
 data_dir:/data/mysql/mysql3376/data/
 base_dir:/usr/local/mysql

2.先在主库上导出，并将导出文件复制到从库

mysqldump -u root -psafe_2016 -S /tmp/mysql3376.sock --master-data=2 --single-transaction -A > A0426.sql

scp A0426.sql root@10.16.24.108:/data/mysql/mysql3376/data/

3.在从库上将导入数据

mysql -u root -psafe_2016 -S /tmp/mysql3376.sock </data/mysql/mysql3376/data/A0426.sql
检查数据库是否有看到：
(product)root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lots               |
| mysql              |
| performance_schema |
| sakila             |
+--------------------+
5 rows in set (0.00 sec)

(product)root@localhost [(none)]>

4.主库上建立复制帐号
grant replication slave on *.* to 'repl'@'10.16.24.%' identified by "repl_safe";

5.修改主从库的相关参数
主库：
log-bin     = /data/mysql/mysql3376/logs/mysql-bin
binlog_format=row
server-id = 1073376

从库：
log-bin     = /data/mysql/mysql3376/logs/mysql-bin
binlog_format=row
server-id = 1083376

主从库修改后，需重启。

6.确定主从复制起点信息
cd /data/mysql/mysql3376/data
more A0426.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000063', MASTER_LOG_POS=99765229;

7.配置复制环境
从库上操作：
change master to
master_host='10.16.24.107',
master_port=3376,
master_user='repl',
master_password='repl_safe',
master_log_file='mysql-bin.000063',
master_log_pos=99765229;

8.启动slave
  start slave;

9.查看状态
(product)root@localhost [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.16.24.107
                  Master_User: repl
                  Master_Port: 3376
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000063
          Read_Master_Log_Pos: 99765436
               Relay_Log_File: relay-bin.000003
                Relay_Log_Pos: 490
        Relay_Master_Log_File: mysql-bin.000063
             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: 99765436
              Relay_Log_Space: 657
              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: 53376
                  Master_UUID: 36af7e42-f4fc-11e5-8b08-0050568a0bcb
             Master_Info_File: /data/mysql/mysql3376/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
1 row in set (0.00 sec)

10.验证同步
在主库上建立database zengxuewen.

在从库查看是否有同步：

(product)root@localhost [(none)]> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| lots               |
| mysql              |
| performance_schema |
| sakila             |
| zengxuewen         |
+--------------------+
6 rows in set (0.00 sec)