?

master與slave拓撲圖，目的使slave能同步master數據

首先，準備兩臺虛擬機。

master=A，IP：192.168.1.200?

slave=B，IP：192.168.1.201

A和B均安裝mysql

yum install mysql-server -y
?

A和B均操作，啟動mysql服務，初始化數據

?/etc/init.d/mysqld start
配置master和salve的文件

vim /etc/my.cnf

A上master的文件，紅色部分都是添加后內容。id為1好區別數據庫主輔角色，開啟binlog

?

?

B上slave的配置文件，id為2主要就是區別角色，不能和master的id一樣，并設置relay_log

A和B均重啟一個mysql服務

service mysqld restart

A上創建一個遠程用戶，權限是replication 。用戶名：user1 密碼：123

mysql> GRANT replication slave ON *.* TO 'user1'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
?

B上數據庫應該是一個新的，把數據全部刪除，然后重啟服務初始化數據

cd /var/lib/mysql
?rm -fr *
?service mysqld restart

A上備份所有數據，-A是備份所有的庫，-x是鎖表

[root@localhost ~]# mysqldump -A -x > /tmp/full.sql

并把數據拷貝到B上
[root@localhost ~]# scp /tmp/full.sql 192.168.1.201:/tmp/
root@192.168.1.201's password:
full.sql?????????????????????????????? 100%? 412KB 412.4KB/s?? 00:00

在B上把從A上拷貝過來的數據導入

[root@localhost mysql]# mysql < /tmp/full.sql

這時候在A上給數據庫加把鎖，查看master狀態，binlog是368.解鎖。

mysql>? flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql>? show master status;
+---------------+----------+--------------+------------------+
| File????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000004 |????? 368 |????????????? |????????????????? |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
?

在B上操作，讓salve找master，binlog的位置。master的IP等等

mysql> change master to master_host='192.168.1.200', master_port=3306, master_user='user1', master_password='123', master_log_file='binlog.000004',master_log_pos=368;
Query OK, 0 rows affected (0.01 sec)
啟動slave

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看slave狀態，IO和SQL均為Yes

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

mysql> show slave status \G
*************************** 1. row ***************************
???????????? Slave_IO_State: Waiting for master to send event
??????????????? Master_Host: 192.168.1.200
??????????????? Master_User: user1
??????????????? Master_Port: 3306
????????????? Connect_Retry: 60
??????????? Master_Log_File: binlog.000004
??????? Read_Master_Log_Pos: 368
???????????? Relay_Log_File: mysql-relay-bin.000002
????????????? Relay_Log_Pos: 232
????? Relay_Master_Log_File: binlog.000004
?????????? 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: 368
??????????? Relay_Log_Space: 232
??????????? 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: 0
1 row in set (0.00 sec)
?

在A上新建一個數據庫

mysql> create database nimei;
Query OK, 1 row affected (0.00 sec)
在B上查看數據庫，數據庫nimei同步過來了。

mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| mysql????????????? |
| nimei????????????? |
| test?????????????? |
+--------------------+
4 rows in set (0.00 sec)
假如把B上數據庫服務停止了

[root@localhost mysql]# service mysqld stop
停止 MySQL：?????????????????????????????????????????????? [確定]
然后在A上新建一個數據庫niba

mysql> create database niba;
Query OK, 1 row affected (0.00 sec)
在B上把mysql服務開啟

[root@localhost mysql]# service mysqld start
啟動 MySQL：?????????????????????????????????????????????? [確定]
查看slave的狀態以及數據庫

IO和SQL運行狀態均為Yes

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
這是A上新建數據庫niba也同步過來了

mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| mysql????????????? |
| niba?????????????? |
| nimei????????????? |
| test?????????????? |
+--------------------+
5 rows in set (0.00 sec)
?

?

轉載于:https://blog.51cto.com/noodle/1076062

總結

以上是生活随笔為你收集整理的MySQL-AB的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。