|
|
单主到多备： Master-MultiSlave
Master Slave1
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝====＝＝＝＝＝＝＝＝＝＝＝
IP 192.168.0.194 192.168.0.195
Server_ID 10 20
1. 主库
[root@master ~]# vim /etc/my.cnf
log-bin=master-bin
server-id = 10
[root@master ~]# service mysqld restart
[root@master ~]# mysql
mysql> grant replication slave, replication client on *.*
-> to datura@'192.168.0.%' identified by '123';
mysql> flush privileges;
初始化备库（使其和主库数据一致）：
---------------------------------------------------------------------------------------------------------------------------------------------------------
1. 主库
mysql> flush tables with read lock; //主服务器锁定表
[root@master ~]# mysqldump --all-databases > all.sql //备份所以数据
[root@master ~]# mysql -e 'show master status' //查看偏移量
+------------------------------+------------+---------------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------------+------------+--------------------+---------------------------+
| master-bin.000001 | 699 | | |
+------------------------------+-----------+---------------------+---------------------------+
mysql> unlock tables; //解锁表
[root@master ~]# rsync -va all.sql 192.168.0.195:/
2. 备库
[root@slave1 ~]# vim /etc/my.cnf
server-id = 20
[root@slave1 ~]# service mysqld start
[root@slave1 ~]# mysql
mysql> reset master;
mysql> source /all.sql //恢复所有数据到备库
mysql> change master to
master_host='192.168.0.194',
master_user='datura',
master_password='123',
master_log_file='master-bin.000001',
master_log_pos=699;
Query OK, 0 rows affected (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: 192.168.0.195
Master_User: datura
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 354
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 500
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
三、常见复制拓朴
=================================================================
1. 一主库多备库
|-------> 备库
主库 ------>|-------> 备库
|-------> 备库
2. 主库，分发主库以及备库
|-------> 备库
主库 ------> 分发主库------|-------> 备库
（blackhole） |-------> 备库
3. 主——主复制（双主）
（备库）主库 <------------------> 备库（主库）
四、MySQL 主主同步
===============================================================
重置数据库：
[root@mysql ~]# service mysqld stop
[root@mysql ~]# rm -rf /usr/local/mysql/data/*
[root@mysql ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql \
> --datadir=/usr/local/mysql/data
1. mysql1 192.168.0.194:
[root@mysql1 ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id = 10
[root@mysql1 ~]# service mysqld start
Starting MySQL..... [ OK ]
[root@mysql1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17-log Source distribution
Copyright (c) 2000, 2014, 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>
mysql> reset master;
Query OK, 0 rows affected (0.42 sec)
mysql> grant replication slave, replication client on *.*
-> to datura@'192.168.0.%' identified by '123';
Query OK, 0 rows affected (0.10 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql> change master to
-> master_host='192.168.0.194',
-> master_user='datura',
-> master_password='123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.80 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
Level: Note
Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.03 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.195
Master_User: datura
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
2. mysql2 192.168.0.195:
[root@mysql2 ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id = 20
[root@mysql2 ~]# service mysqld start
Starting MySQL..... [ OK ]
[root@mysql2 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17-log Source distribution
Copyright (c) 2000, 2014, 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>
mysql> reset master;
Query OK, 0 rows affected (0.39 sec)
mysql> grant replication slave, replication client on *.*
-> to datura@'192.168.0.%' identified by '123';
Query OK, 0 rows affected (0.20 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql> change master to
-> master_host='192.168.0.194',
-> master_user='datura',
-> master_password='123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.90 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
Level: Note
Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.04 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.194
Master_User: datura
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
4. mysql1,mysql2:
mysql1:
----------------------------------------------------------------------------------------------------------------
mysql> start slave;
Query OK, 0 rows affected (0.15 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.195
Master_User: datura
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql2:
--------------------------------------------------------------------------------------------------------------------
mysql> start slave;
Query OK, 0 rows affected (0.17 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.194
Master_User: datura
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4. 测试
MySQL1：
-----------------------------------------------------------------------------------------------------------------------------------------------
mysql> show processlist\G
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 263
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 263
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)
mysql> create database bbs;
Query OK, 1 row affected (0.00 sec)
mysql> create table bbs.table1(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into bbs.table1 values(100);
Query OK, 1 row affected (0.02 sec)
mysql> select * from bbs.table1;
+----------+
| id |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)
MySQL2：
------------------------------------------------------------------------------------------------------------------------------------------------------
mysql> show processlist\G
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 263
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 263
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)
mysql> select * from bbs.table1;
+----------+
| id |
+----------+
| 100 |
+----------+
1 row in set (0.03 sec)
mysql> insert into bbs.table1 values(200);
Query OK, 1 row affected (0.35 sec)
mysql> select * from bbs.table1;
+----------+
| id |
+----------+
| 100 |
| 200 |
+----------+
2 rows in set (0.00 sec)
MySQL1：
-----------------------------------------------------------------------------------------------------------------------------------------------
mysql> select * from bbs.table1;
+----------+
| id |
+----------+
| 100 |
| 200 |
+----------+
2 rows in set (0.00 sec)
5. 建立用于客户连接用户
mysql> grant ALL on *.* to admin@'192.168.0.%' identified by '456';
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
客户端登录测试
----------------------------------------------------------------------------------------------------------------------------------
# mysql -h 192.168.0.194 -uadmin -p456;
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.17-log Source distribution
Copyright (c) 2000, 2012, 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>
mysql> select * from bbs.table1;
+----------+
| id |
+----------+
| 100 |
| 200 |
+----------+
2 rows in set (0.00 sec)
# mysql -h 192.168.0.195 -uadmin -p456;
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.17-log Source distribution
Copyright (c) 2000, 2012, 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>
mysql> select * from bbs.table1;
+----------+
| id |
+----------+
| 100 |
| 200 |
+----------+
2 rows in set (0.00 sec)
|
|
發表於 2019-9-29 16:11:50