MySQl 主从同步配置

一、环境准备

单机安装2个MySQL 实例；
主机ip: 172.17.194.17
MySQL 实例1： 端口13306 (主)；
MySQL 实例2： 端口13307 (从)；

二、 主节点配置

1. 修改主节点的 my.cnf

server_id = 1                              # 集群中唯一 id，用1代表主节点
binlog-format = ROW                        # 行级 binlog
log_bin = /data/mysql13306/log/binlog      # binlog的位置
binlog_ignore_db =  mysql                  # 忽略mysql库的binlog

2. 重启 MySQL

service mysql13306 restart

3. 查看主节点状态

mysql> show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000004 |      194 |              | mysql            | 8ea68ca4-363c-11e8-9bec-000c29f85fb6:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

4. 主节点创建同步用户

mysql> grant replication slave on *.* to 'user'@'172.17.194.17' identified by 'password';
mysql> flush privileges;

三、从节点配置

1. 修改从节点的 my.cnf

server_id = 2
log_bin = /data/mysql13307/log/binlog  
replicate_ignore_db = mysql 
slave_skip_errors = all

2. 重启从节点MySQL

service mysql13307 restart

3. 配置主从关联

mysql> change master to master_host='172.17.194.17',master_user='user',master_password='password',master_port=13306;

其他配置参数：change master 参数

4. 启动从节点

mysql> start slave;
Query OK, 0 rows affected (0.03 sec) 

5. 查看从节点状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.194.17
                  Master_User: user
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 642
               Relay_Log_File: relaylog.000005
                Relay_Log_Pos: 849
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: mysql.%,test.% 
...

四、 验证主从同步

1. 主节点初始化数据

mysql> create database mydb;
Query OK, 1 row affected (0.01 sec)

mysql> use mydb
Database changed
mysql> create table userinfo(id int, t_name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> create table user_info(id int, t_name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into user_info values(1, 'wille');
Query OK, 1 row affected (0.00 sec)

2. 从节点验证同步情况

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use mydb;
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> select * from user_info;
+------+--------+
| id   | t_name |
+------+--------+
|    1 | wille  |
+------+--------+
1 row in set (0.00 sec)

最后，主从同步还有许多配置，没有涉及，具体要按实际情况选用部分配置