|
|
===================================================================
重置數據庫：
- rm -rf /usr/local/mysql/data/*
- /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql
一、主/備均为剛初始的數據庫
================================================================
单主到多备： Master-MultiSlave
Master Slave1 Slave2
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
IP 192.168.0.194 192.168.0.195 192.168.0.196
Server_ID 10 20 30
1. 主库
Master：
------------------------------------------------------------------------------------------------------------------
[root@master ~]#
log-bin=master-bin //啟用bin-log日志，命名為master-bin
server-id = 10 //指定主服務器id
[root@master ~]#
Starting MySQL..... [ OK ]
[root@master ~]#
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.
//删除bin-log日志
mysql>Query OK, 0 rows affected (0.24 sec)
//授权登录的从服务器以及帐号密码
mysql>- grant replication slave, replication client on *.* to datura@'192.168.0.%' identified by '123';
//刷新授权表
mysql>Query OK, 0 rows affected (0.01 sec)
2. 备库
Slave1：
-------------------------------------------------------------------------------------------------------------------------------------------------------
a. 测试复制账号
[root@slave1 ~]#- mysql -h 192.168.0.194 -udatura -p123
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 2
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>
b. 配置复制
[root@slave1 ~]#
server-id = 20 //指定从服务器id
[root@slave1 ~]#
Starting MySQL...... [ OK ]
[root@slave1 ~]#
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17 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; //删除bin-log日志
ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER
mysql> change master to //改变bin-log日志的获取路径
-> master_host='192.168.0.194', //指定主服务器地址
-> master_user='datura', //指定登录用户
-> master_password='123', //指定登录密码
-> master_log_file='master-bin.000001', //指定读取的bin-log日志
-> master_log_pos=0; //指定bin-log日志的偏移量
Query OK, 0 rows affected, 2 warnings (0.44 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.00 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: master-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: slave1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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.194
Master_User: datura
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 431
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 595
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave2：
---------------------------------------------------------------------------------------------------------------------------------------------
a. 测试复制账号
[root@slave2 ~]# mysql -h 192.168.0.194 -udatura -p123
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.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>
b. 配置复制
[root@slave1 ~]# vim /etc/my.cnf
server-id = 30
[root@slave1 ~]# service mysqld start
Starting MySQL...... [ OK ]
[root@slave1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17 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; //删除bin-log日志
ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER
mysql> change master to //改变bin-log日志的获取路径
-> master_host='192.168.0.194', //指定主服务器地址
-> master_user='datura', //指定登录用户
-> master_password='123', //指定登录密码
-> master_log_file='master-bin.000001', //指定读取的bin-log日志
-> master_log_pos=0; //指定bin-log日志的偏移量
Query OK, 0 rows affected, 2 warnings (0.44 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.00 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: master-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: slave2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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.122.10
Master_User: datura
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 431
Relay_Log_File: slave2-relay-bin.000002
Relay_Log_Pos: 595
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3. 测试
Master：
-----------------------------------------------------------------------------------------------------------------------------------------------
mysql> show processlist\G
*************************** 2. row ***************************
Id: 4
User: datura
Host: 192.168.0.195:37472
db: NULL
Command: Binlog Dump
Time: 532
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 3. row ***************************
Id: 5
User: datura
Host: 192.168.0.196:34148
db: NULL
Command: Binlog Dump
Time: 110
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
3 rows in set (0.05 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)
Slave1：
------------------------------------------------------------------------------------------------------------------------------------------------------
mysql> show processlist\G
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 678
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 239636
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)
Slave2：
-----------------------------------------------------------------------------------------------------------------------------------------------------
mysql> show processlist\G。
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 396
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 239777
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.03 sec)
mysql> select * from bbs.table1;
+----------+
| id |
+----------+
| 100 |
+----------+
1 row in set (0.03 sec)
|
|
發表於 2019-9-29 15:58:56