mysql之主从复制 简书_MySQL主从复制（传统）

拓扑结构：(一主多从)

Mysql-master01  (192.168.43.123)

Mysql-slave01     (192.168.43.124)

Mysql-slave02     (192.168.43.125)

版本信息：

OS版本      ： CentOS release 6.10 最小化安装

Mysql版本： mysql-5.7.31-1.el6.x86_64.rpm-bundle.tar

一、系统调整(测试)

适用范围：所有服务器

1.关闭防火墙

2.修改/etc/selinux/config关闭selinux

3.其他工具软件的安装 vim openssh-* net-tools 等

二、Mysql的安装

适用范围：所有服务器

1.安装

[root@localhost opt]# tar -xf mysql-5.7.31-1.el6.x86_64.rpm-bundle.tar

[root@localhost opt]# yum install mysql-community-* -y

2.root密码调整

[root@localhost opt]# /etc/init.d/mysqld start

Initializing MySQL database:                              [  OK  ]

Starting mysqld:                                          [  OK  ]

[root@localhost opt]#

mysql> alter user 'root'@'localhost' identified by 'Test.123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

三、配置主从复制(传统)

1.配置调整

主服务器配置：

[root@localhost opt]# mkdir /var/log/mysql

[root@localhost opt]# id mysql

uid=27(mysql) gid=27(mysql) groups=27(mysql)

[root@localhost opt]# chown mysql.mysql /var/log/mysql

[root@localhost opt]# cat /etc/my.cnf

[mysqld]

log-bin=/var/log/mysql/mysql-bin

server-id=1

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

default-storage-engine = INNODB

character-set-server = utf8

collation-server = utf8_general_ci

[client]

default-character-set = utf8

[root@localhost opt]# /etc/init.d/mysqld restart

Stopping mysqld:                                          [  OK  ]

Starting mysqld:                                          [  OK  ]

[root@localhost opt]#

从服务器配置：

[root@localhost opt]# mkdir /var/log/mysql

[root@localhost opt]# id mysql

uid=27(mysql) gid=27(mysql) groups=27(mysql)

[root@localhost opt]# chown mysql.mysql /var/log/mysql

[root@localhost opt]# cat /etc/my.cnf

[mysqld]

log-bin=/var/log/mysql/mysql-bin

server-id=2

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

default-storage-engine = INNODB

character-set-server = utf8

collation-server = utf8_general_ci

[client]

default-character-set = utf8

[root@localhost opt]# /etc/init.d/mysqld restart

Stopping mysqld:                                          [  OK  ]

Starting mysqld:                                          [  OK  ]

[root@localhost opt]#

2.复制用户创建(主服务器配置)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by 'Test.123';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql>

从服务器测试一下：

[root@localhost opt]# mysql -urepl -p'Test.123' -h 192.168.43.123

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 4

Server version: 5.7.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>

3.将从服务器加入新服务器(2台从服务器)

[root@localhost opt]# mysql -uroot -p'Test.123'

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.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> system clear

mysql>  FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.00 sec)

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)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.43.123', MASTER_USER='repl', MASTER_PASSWORD='Test.123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

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)

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.43.123

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 872

Relay_Log_File: localhost-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: 1249

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: 471

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: 04baf52c-e083-11ea-9564-000c29298862

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Waiting for global read lock

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> show binlog events\G;

*************************** 1. row ***************************

Log_name: mysql-bin.000001

Pos: 4

Event_type: Format_desc

Server_id: 2

End_log_pos: 123

Info: Server ver: 5.7.31-log, Binlog ver: 4

*************************** 2. row ***************************

Log_name: mysql-bin.000001

Pos: 123

Event_type: Previous_gtids

Server_id: 2

End_log_pos: 154

Info:

2 rows in set (0.00 sec)

ERROR:

No query specified

mysql>

mysql> unlock tables;

Query OK, 0 rows affected (0.01 sec)

四、测试

主服务器操作：

mysql> create database testrep;

Query OK, 1 row affected (0.00 sec)

mysql> use testrep;

Database changed

mysql> create table t(id int primary key,name varchar(20));

Query OK, 0 rows affected (0.02 sec)

mysql> desc t;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | int(11)    | NO  | PRI | NULL    |      |

| name  | varchar(20) | YES  |    | NULL    |      |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.04 sec)

mysql> insert into t values(1,'Pony');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(2,'Allen');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(3,'Peter');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t;

+----+-------+

| id | name  |

+----+-------+

|  1 | Pony  |

|  2 | Allen |

|  3 | Peter |

+----+-------+

3 rows in set (0.00 sec)

mysql>

从服务器查询：

mysql> show databases;

+--------------------+

| Database          |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| testrep            |

+--------------------+

5 rows in set (0.00 sec)

mysql> use testrep;

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> show tables;

+-------------------+

| Tables_in_testrep |

+-------------------+

| t                |

+-------------------+

1 row in set (0.00 sec)

mysql> select * from t;

+----+-------+

| id | name  |

+----+-------+

|  1 | Pony  |

|  2 | Allen |

|  3 | Peter |

+----+-------+

3 rows in set (0.00 sec)

附：binlog日志转sql文件

[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v  mysql-bin.000001 --result-file=1.sql

备注：如果在配置主从复制之前，主服务器存在前期数据，需要同步到从服务器，保持主从服务器数据一致后再进行同步操作。此间，可用同步的工具可选用mysqldump、冷备份、xtrabackup等工具，这其中又需要考虑是MyISAM表还是InnoDB表。

参阅：

https://www.jianshu.com/p/faf0127f1cb2

https://www.cnblogs.com/f-ck-need-u/p/9155003.html