分享一个Mysql主从同步记录

操作:centos 6.5 mysql版本:5.7.18

准备两台mysql,当然可以配置多台，下面以两台为例

master:192.168.43.66

slave:192.168.43.67

一、首先配置主服务器master(192.168.43.66)：

创建同步用户账号

 $mysql -uroot -p
 mysql> use mysql
 mysql> grant replication slave on *.* to 'master'@'192.168.43.67' identified by '12345678';
 mysql>flush privileges;

修改/etc/my.cnf配置文件vim /etc/my.cnf

增加以下参数

server-id=1  #主服务器id
log-bin=mysql-bin  #启动mysql二进制日志系统，
binlog-do-db=my_data #需要同步的数据库

重启mysql

$ service mysqld restart

查看master数据库状态

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| file             | position | binlog_do_db | binlog_ignore_db | executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      453 | my_data      |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

导出数据库my_data

二、现在开始配置slave(192.168.43.67):

vi /etc/my.cnf

server-id=2  #从服务器id
log-bin=mysql-bin  #启动mysq二进制日志系统
replicate-do-db=my_data #需要同步的数据库名

导入数据库my_data

配置主从同步:

$ mysql -uroot -p
mysql>use mysql 
mysql>stop slave;
mysql>change master to
      master_host='192.168.43.66',
      master_user='master',
      master_password='12345678',
      master_log_file='mysql-bin.000005',
      master_log_pos=154;   
mysql>start slave;

#log_file与log_pos是主服务器master状态下的file与position

执行start slave;时可能会报错：

slave failed to initialize relay log info structure from the repository

此时需要在/etc/my.cnf中加入配置

relay_log=/data/logs

查看从数据库状态

mysql>show slave status\g;

*************************** 1. row ***************************
               slave_io_state: waiting for master to send event
                  master_host: 192.168.43.66
                  master_user: zhangrui
                  master_port: 3306
                connect_retry: 60
              master_log_file: mysql-bin.000005
          read_master_log_pos: 154
               relay_log_file: slave01-relay-bin.000002
                relay_log_pos: 320
        relay_master_log_file: mysql-bin.000005
             slave_io_running: yes
            slave_sql_running: yes
              replicate_do_db: my_data
          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: 154
              relay_log_space: 529
              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: 9602f224-90b9-11e7-861d-000c29fa24a3
             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)

注意查看slave_io_running: yes slave_sql_running: yes 这两项必须为yes 以及log_file、log_pos要于master状态下的file,position相同