Linux 下實現Mysql主從同步,linuxmysql主從同步
一、實驗環境準備:
主庫(Master):CentOS release 5.9 x86_64
IP:10.45.172.40
mysql Ver 14.12 Distrib 5.0.95
從庫CentOS release 5.9 x86_64
IP:10.45.172.37
mysql Ver 14.12 Distrib 5.0.95,
測試是否安裝mysql:service mysql restart 或servicemysqld restart
安裝mysql安裝:
在官方網站下載以下安裝包 http://dev.mysql.com/downloads/mysql#downloads
rpm -ivh MySQL-server-5.5.25a-1.rhel5.x86_64 MySQL-client-5.5.25a-1.rhel5.x86_64 MySQL-devel-5.5.25a-1.rhel5.x86_64
設定修改密碼 /usr/bin/mysqladmin -u root password‘rootadmin’
二、主庫的操作
1.查看主程式庫伺服器/usr/share/mysql目錄下的cnf檔案
#ll /usr/share/mysql/*.cnf
其中.cnf文檔如下:
my-small.cnf 記憶體少於或等於64M,只提供很少的的資料庫服務
my-medium.cnf 記憶體在32M--64M之間而且和其他服務一起使用,例如web
my-large.cnf 記憶體有512M主要提供資料庫服務
my-huge.cnf 記憶體有1G到2G,主要提供資料庫服務
my-innodb-heavy-4G.cnf 記憶體有4G,主要提供較大負載資料庫服務(一般伺服器都使用這個)
2.複製檔案到/etc下並更名為my.cnf
#cp /usr/share/mysql/my-innodb-heavy-4G.cnf/etc/my.cnf
3.修改my.cnf
#vi /etc/my.cnf 修改以下參數:
server-id = 1
log-bin=mysql-bin
binlog-do-db=test#需要同步的資料庫,如果同步多個庫,需要配置多行
配置完重啟:
service mysql restart
4.建立同步用的資料庫賬戶
mysql> grant replication slave on *.* to'testuser'@'10.45.172.37' identified by 'test123';
Query OK, 0 rows affected (0.00 sec)
5.鎖住主庫表,停止資料更新。
mysql> flush tables with read lock;
6.顯示主庫資訊並記錄
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 566 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
將上面的日誌名mysql-bin.000001和位置566記錄下來,從庫配置時要用到
7.備份所有庫檔案並複製到從程式庫伺服器上
在從庫上執行:
cd /var/lib/mysql
mkdir /tmp/bak
mv */tmp/bak
備份後,從主庫複製過來:
scp -r root@10.45.172.40:/var/lib/mysql/*./
8.資料庫複寫完成後對主庫所有表解鎖
#unlock tables;
三、從庫的操作
1.修改設定檔:
#vi /etc/my.cnf
server-id = 2 將這個ID號改為2
log-bin=mysql-bin 此項如果開啟建議關閉
master-host = 10.45.172.40 配置主庫的IP
master-user = testuser 同步用的賬戶
master-password = test123 同步用的賬戶密碼
master-port = 3306 同步資料庫的連接埠號碼
2.調整許可權
#chown -R mysql:mysql /var/lib/mysql
3.重啟mysql服務
#service mysql restart
4.手動同步
mysql>slave stop
mysql> CHANGE MASTER TO
MASTER_HOST='10.45.172.40',
MASTER_USER='testuser',
MASTER_PASSWORD='test123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=471,
MASTER_CONNECT_RETRY=60;
5.啟動從庫
mysql>slave start;
6.檢查從庫是否正常同步
mysql> show slave status \G;
*************************** 1. row***************************
Slave_IO_State: Waiting for master to sendevent
Master_Host: 10.45.172.40
Master_User: testuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos:471
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000001
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:471
Relay_Log_Space: 235
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)
四、測試
1.主庫執行
mysql> use test
Database changed
mysql> create table tt(id int,namevarchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tt values(1,'jhp');
Query OK, 1 row affected (0.00 sec)
2.在從庫中檢查是否同步過來:
mysql> select * from test.tt;
+------+------+
| id | name |
+------+------+
| 1 | jhp |
+------+------+
1 row in set (0.00 sec)