Mysql5.7主从同步配置实战

主库：172.31.28.5

从库：172.31.17.79

主从库都需要这样安装

下载安装包

 wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.7.18-linux-glibc2.5-x86_64 mysql

添加用户组

groupadd mysql
useradd -g mysql my4840

创建数据目录

mkdir -p /data/mysql4840

给数据目录赋权

chown -R my4840:mysql /data/mysql4840

初始化数据库

[root@ip-172-31-28-5 mysql]# ./bin/mysqld --user=my4840 --basedir=/usr/local/mysql --datadir=/data/mysql4840/data --initialize
2018-05-28T09:40:38.480254Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-28T09:40:38.731882Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-05-28T09:40:38.773896Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-05-28T09:40:38.833075Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2e038ca2-625b-11e8-a2f4-0240eb101cc2.
2018-05-28T09:40:38.834853Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-05-28T09:40:38.835227Z 1 [Note] A temporary password is generated for root@localhost: )x0UI9Ulqp<a

初始化的密码需要记住哈

下来将mysql服务添加到系统中

[root@ip-172-31-28-5 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@ip-172-31-28-5 mysql]# ldconfig
[root@ip-172-31-28-5 mysql]# echo "PATH=$PATH:/usr/local/mysql/bin" > /etc/profile.d/mysql.sh
[root@ip-172-31-28-5 mysql]# source /etc/profile.d/mysql.sh
[root@ip-172-31-28-5 mysql]# chkconfig mysqld on

增加mysql配置文件

vi /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql #mysql路径
datadir=/data/mysql4840/data #mysql数据目录
socket=/tmp/mysql4840.sock
user=my4840
server_id=100 #MySQLid 同一个网段的id不能重复，配置主从的时候也不能一样
port=4840
pid_file=/data/mysql4840/data/mysql.pid

启动数据库

[root@ip-172-31-28-5 mysql]# service mysqld start
Starting MySQL.Logging to '/data/mysql4840/data/ip-172-31-28-5.err'.
 SUCCESS!

修改密码

[root@ip-172-31-28-5 mysql]# mysql -uroot -peu,vraDH-2G% -P 4840 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password='123456';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

主库

添加开启binlog

[xubo-iri@ip-172-31-28-5 ~]$ more /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql #mysql路径
datadir=/data/mysql4840/data #mysql数据目录
socket=/tmp/mysql4840.sock
user=my4840
server_id=100 #MySQLid 后面2个从服务器需设置不同
port=4840
pid_file=/data/mysql4840/data/mysql.pid
log-bin=mysql-bin

重启服务

service mysqld restart

查看binlog是否开启

[root@ip-172-31-28-5 xubo-iri]# mysql -uroot -p123456 -P 4840 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql4840/data/mysql-bin |
| log_bin_index | /data/mysql4840/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)

重建主从复制账户

mysql> GRANT REPLICATION SLAVE ON *.* TO'replica'@'172.31.17.79' IDENTIFIED BY '11111111111111111';
mysql> FLUSH PRIVILEGES;
mysql> use mysql
mysql> select user,authentication_string,host from user;
+-----------+-------------------------------------------+--------------+
| user | authentication_string | host |
+-----------+-------------------------------------------+--------------+
| root | *C1EE34FAC5D8004DBE7A95DC607992DFDFDFD | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCFDFDSF | localhost |
| replica | *C2853DFA82C3BA0597A50A1ECDFDSFSDF | 172.31.17.79 |
+-----------+-------------------------------------------+--------------+

查看master状态

mysql> show master status\G
*************************** 1. row ***************************
 File: mysql-bin.000001
 Position: 154
 Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

从库

配置文件如下：

[root@ip-172-31-17-79 mysql]# vi /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql #mysql路径
datadir=/data/mysql4840/data #mysql数据目录
socket=/tmp/mysql4840.sock
user=my4840
server_id=101 #MySQLid 同一个网段的id不能重复，配置主从的时候也不能一样
port=4840
pid_file=/data/mysql4840/data/mysql.pid
read_only = 1
master_info_repository=TABLE
relay_log_info_repository=TABLE

登录到mysql

 mysql -uroot -p123456 -P 4840 -h127.0.0.1

设置主信息

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to
 -> master_host='172.31.28.5',
 -> master_user='replica',
 -> master_password='0ff4f09e02111111',
 -> master_port=4840,
 -> master_log_file='mysql-bin.000001',
 -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

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

mysql> show slave status \G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.31.28.5
 Master_User: replica
 Master_Port: 4840
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 154
 Relay_Log_File: ip-172-31-17-79-relay-bin.000002
 Relay_Log_Pos: 320
 Relay_Master_Log_File: mysql-bin.000001
 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: 154
 Relay_Log_Space: 537
 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: 100
 Master_UUID: 3c63b807-625d-11e8-8f21-0240eb101cc2
 Master_Info_File: mysql.slave_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)

好，到此结束，我们测试一下：

主库上创建一个库，如下：

mysql> create database hello;
Query OK, 1 row affected (0.00 sec)

我们再看看从库，如下：

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hello |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)

好，ok了

另外就是配置文件的说明，可以单独同步某一个库甚至表，也可以不同步某一个库，如下：

#作为主机的配置

binlog-do-db=hello #要给从机同步的库

binlog-ignore-db=mysql #不给从机同步的库(多个写多行)