浮雲雅築: [研究] MySQL 5.1.71 資料庫同步(Replication)-主從模式(CentOS 6.5 x86

[研究] MySQL 5.1.71 資料庫同步(Replication)-主從模式(CentOS 6.5 x86_64)

2014-01-29
Lu

參考
16.1.1. How to Set Up Replication
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html

環境：
server1 : CentOS 6.5 x86_64 , IP: 192.168.128.101
server2 : CentOS 6.5 x86_64 , IP: 192.168.128.102

CentOS 6.5 的 MySQL 是 5.1.71 版

[root@centos1 ~]# yum list | grep mysql-server
mysql-server.x86_64 5.1.71-1.el6 base
[root@centos1 ~]#

主從模式：A->B (A有新增資料，B也會有)

開始安裝
----------------------------------------------------------------------------

先在兩台上都安裝啟動 MySQL，暫時先關閉防火牆

yum -y install mysql mysql-server
service mysqld start
chkconfig mysqld on
# 替 MySQL 帳號 root 設定密碼為 123456 (依個人喜好)
/usr/bin/mysqladmin -u root password '123456'
service iptables stop

----------------------------------------------------------------------------

假設要同步的資料庫是 backup

Server1 (MySQL Master, 192.168.128.101)上

1. 建立同步用帳號和要同步的資料庫backup，
讓另一台主機192.168.128.102上的root帳號在這台上有REPLICATION SLAVE權限
(-u 參數後面是帳號，-p 參數後面是密碼)

[root@server1 ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, 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> CREATE USER 'root'@'192.168.128.102' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.128.102';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> create database backup;
Query OK, 1 row affected (0.00 sec)

mysql> \q
Bye
[root@server1 ~]#

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.128.102';
可用
mysql> GRANT FILE ON *.* TO 'root'@'192.168.128.102';
或
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.128.102';
替代

PS :
如果 backup 資料庫已經存在使用，請用 mysqldump 將資料庫匯出，

[root@server1 ~]# mysqldump backup -uroot -p123456 > backup.sql

在 Server2 上把 backup 資料庫匯入，然後進行工作

[root@server2 ~]# mysql backup -uroot -p123456 < backup.sql

否則 Server1 原來 backup 已經存在的資料，是不會同步過去的，只會同步新增的資料

如果要備份整個資料庫，可用

[root@server1 ~]# mysqldump -uroot -p123456 --all-databases --add-drop-database --lock-all-tables > fulldb.sql
[root@server2 ~]# mysql -uroot -p123456 < fulldb.sql

或把Server2 上/var/lib/mysql中檔案砍光，把 Server1 上 /var/lib/mysql 整個目錄拷貝覆蓋掉 Server2 上的 (MySQL 的版本最好相同，否則可能會毀掉資料庫)

或執行

[root@server1 ~]# rsync -rvlHpogDtS --delete -e ssh /var/lib/mysql/. root@192.168.128.102:/var/lib/mysql/.

以本機器/var/lib/mysql完全同步到另一台的

2. 修改 /etc/my.cnf，在[mysqld]中增加server-id和log-bin(這兩個必須)，binlog-do-db指定需要日誌的資料庫(非必須)

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#設定一個編號
server-id=1
#讓別機器同步用的資料寫入此log中
log-bin=mysql-bin
#讓別機器同步用的資料庫backup (不設定這行，表示所有資料庫)
binlog-do-db=backup

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

3.重新啟動資料庫，讓新建的帳號和my.cnf生效

[root@server1 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server1 ~]#

4.檢查日誌情況，應該會顯示某些東西(顯示Empty則失敗)

[root@server1 ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.71-log Source distribution

Copyright (c) 2000, 2013, 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> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | backup | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
[root@server1 ~]#

----------------------------------------------------------------------------

Server2 (MySQL Slave, 192.168.128.102)上

1.建立要同步的資料庫

[root@server2 ~]# mysql -e "create database backup;" -uroot -p123456

2. 修改 /etc/my.cnf，在[mysqld]中增加下面資訊

[mysqld]
#定期去192.168.128.101抓資料用的帳號、密碼、port、間隔時間、抓哪個資料庫(不設則抓所有資料庫)
server-id=2
master-host=192.168.128.101
master-user=root
master-password=123456
master-port=3306
master-connect-retry=60
replicate-do-db=backup

3.重新啟動資料庫

[root@server2 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server2 ~]#

4.檢查日誌情況，應該會顯示某些東西

(注意，在 server1 指令是 show master status，此處為 slave )

[root@server2 ~]# mysql -e "show slave status;" -uroot -p123456
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | 192.168.128.101 | root | 3306 | 60 | mysql-bin.000001 | 106 | mysqld-relay-bin.000002 | 251 | mysql-bin.000001 | Yes | Yes | backup | | | | | | 0 | | 0 | 106 | 407 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
[root@server2 ~]#

----------------------------------------------------------------------------

現在可以測試了，在 Server1 上 backup 資料庫隨便新增東西，Server2上backup資料庫也會出現(應該不會超過60秒)。

如果無法正常運作，請檢視兩台機器的/var/log/mysqld.log檔案中有關[ERROR]的部分。

當一台電腦重新啟動MySQL時，請等它啟動完後成，再去重新啟動另外一台，否則會有錯誤訊息。

如果mysqld.log不是必須保留的(或把它另外先備份)，可以把它先清空，然後才執行service mysqld restart，這樣比較容易看出是否有錯誤訊息。

(網路上文章說，如果要修改slave的設定，要先刪除/var/lib/mysql/master.info檔案，否則無法生效，但是好像不需要)

Server1 上建立 students 資料表

[root@server1 ~]# mysql -e "use backup; create table students(no int, cname varchar(10)); show tables;" -uroot -p123456
+------------------+
| Tables_in_backup |
+------------------+
| students |
+------------------+
[root@server1 ~]#

[root@server1 ~]# mysql -e "show master status;" -uroot -p123456
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 219 | backup | |
+------------------+----------+--------------+------------------+

[root@server1 ~]# mysql -e "use backup; insert into students values ('01', 'John'); " -uroot -p123456

[root@server1 ~]# mysql -e "show master status;" -uroot -p123456
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 326 | backup | |
+------------------+----------+--------------+------------------+

到 Server2 上檢查

[root@server2 ~]# mysql -e "use backup; show tables;" -uroot -p123456
+------------------+
| Tables_in_backup |
+------------------+
| students |
+------------------+
[root@server2 ~]#

[root@server2 ~]# mysql -e "show slave status;" -uroot -p123456
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | 192.168.128.101 | root | 3306 | 60 | mysql-bin.000001 | 219 | mysqld-relay-bin.000002 | 364 | mysql-bin.000001 | Yes | Yes | backup | | | | | | 0 | | 0 | 219 | 520 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
[root@server2 ~]#

(完)

[研究] MySQL 5.1.71 資料庫同步(Replication)-雙機互備模式(CentOS 6.5 x86_64)
http://shaurong.blogspot.com/2014/01/mysql-5171-replication-centos-65-x8664.html
http://forum.icst.org.tw/phpbb/viewtopic.php?f=21&t=80715

[研究] MySQL 5.1.71 資料庫同步(Replication)-主從模式(CentOS 6.5 x86_64)