mysql 5.7.9 主从配置_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

初始化的密码需要记住哈

下来将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 #不给从机同步的库(多个写多行)