2014年1月29日 星期三

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

[研究] 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) 
http://shaurong.blogspot.com/2014/01/mysql-5171-replication-centos-65-x8664.html
http://forum.icst.org.tw/phpbb/viewtopic.php?f=21&t=80714

[研究] mysql-5.6.15.tar.gz 安裝(CentOS 6.5 x64)
http://shaurong.blogspot.tw/2014/01/mysql-5615targz-centos-65-x64.html
http://forum.icst.org.tw/phpbb/viewtopic.php?f=21&t=80713

[研究] MySQL 5.6.15 安裝(rpm)(CentOS 6.5 x64)
http://shaurong.blogspot.com/2014/01/mysql-5615-rpmcentos-65-x64.html

[研究] MySQL Community 5.6.15 版安裝(yum) (CentOS 6.5 x64)
http://shaurong.blogspot.com/2014/01/mysql-community-5615-tgz-centos-65-x64.html

[研究] 4主機 MySQL Cluster 7.3.3 架設(CentOS 6.5 x64)
http://shaurong.blogspot.tw/2014/01/4-mysql-cluster-7.html

[研究] 4主機 MySQL Cluster 7.2.10.1 架設
http://shaurong.blogspot.tw/2013/02/4-mysql-cluster-7210-1.html

[研究] 2主機 MySQL Cluster 7.2.10-1 架設
http://shaurong.blogspot.com/2013/02/2-mysql-cluster-7210-1.html

[研究] 4主機 MySQL Cluster 7.0.9 架設
http://forum.icst.org.tw/phpbb/viewtopic.php?f=10&t=17903

[研究] 雙主機 MySQL Cluster 7.0.9架設
http://forum.icst.org.tw/phpbb/viewtopic.php?f=10&t=17904

[研究] MariaDB 5.5 安裝(yum)(CentOS 6.3 x64)
http://shaurong.blogspot.com/2013/02/mariadb-55-yum-centos-63-x64.html

[研究] CentOS 5.4 x86 上 MySQL 資料庫同步(Replication)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=17824

[研究] mariadb-5.1.42 (tar.gz)(瑪利亞資料庫, MySQL) 安裝 (CentOS 5.4)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=17988

[研究] mariadb-5.2.4-Linux-i686.tar.gz 安裝 (CentOS 5.5 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=19434

[研究] mysql-5.5.14.tar.gz 安裝(失敗)(CentOS 6.0 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=20252

[研究] mysql-5.5.8.tar.gz 安裝(CentOS 5.5 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=19214

[研究] mysql-5.5.8.tar.gz 安裝(Fedora 14 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=19215

[研究] 每天自動備份 MySQL 方法
http://forum.icst.org.tw/phpbb/viewtopic.php?t=14972



2 則留言:

  1. 請問如果我A Server的DB的資料要與300個ServerDB資料即時做同步,能夠採用這個方式嗎,效能上會不會有問題

    回覆刪除
  2. 技術理論上可以,實際效能我沒測試過;ServerAP 有幾台?為何需要 300 台 ServerDB ?

    回覆刪除