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

拓?fù)浣Y(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)整（測(cè)試）

適用范圍：所有服務(wù)器

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

2.修改/etc/selinux/config關(guān)閉selinux

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

二、Mysql的安裝

適用范圍：所有服務(wù)器

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)

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

1.配置調(diào)整

主服務(wù)器配置：

[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]#

從服務(wù)器配置：

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

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>

從服務(wù)器測(cè)試一下：

[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.將從服務(wù)器加入新服務(wù)器（2臺(tái)從服務(wù)器）

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

四、測(cè)試

主服務(wù)器操作：

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>

從服務(wù)器查詢(xún)：

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

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

參閱：

http://www.itdecent.cn/p/faf0127f1cb2

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

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀(guān)點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。
禁止轉(zhuǎn)載，如需轉(zhuǎn)載請(qǐng)通過(guò)簡(jiǎn)信或評(píng)論聯(lián)系作者。

友情鏈接更多精彩內(nèi)容