MySQL主從復制（傳統(tǒng)）

拓撲結(jié)構(gòu)：（一主多從）

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

一赦役、系統(tǒng)調(diào)整（測試）

適用范圍：所有服務器

1.關(guān)閉防火墻

2.修改/etc/selinux/config關(guān)閉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密碼調(diào)整

[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)

三、配置主從復制（傳統(tǒng)）

1.配置調(diào)整

主服務器配置：

[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.復制用戶創(chuàng)建（主服務器配置）

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)

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)

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;

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

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

| 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日志轉(zhuǎn)sql文件

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

備注：如果在配置主從復制之前互例，主服務器存在前期數(shù)據(jù)奢入，需要同步到從服務器，保持主從服務器數(shù)據(jù)一致后再進行同步操作媳叨。此間腥光，可用同步的工具可選用mysqldump、冷備份糊秆、xtrabackup等工具武福，這其中又需要考慮是MyISAM表還是InnoDB表。

參閱：

http://www.reibang.com/p/faf0127f1cb2

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