Home | Mirror | Search |
過程 1.1. Master 設置步驟
配置 my.cnf 檔案
確保主伺服器主機上my.cnf檔案的[mysqld]部分包括一個log-bin選項。該部分還應有一個server-id=Master_id選項
# vim /etc/mysql/my.cnf server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = test binlog_ignore_db = mysql
bind-address預設是127.0.0.1你必須更改它,否則Slave將無法連結到 Master
#bind-address = 127.0.0.1 bind-address = 0.0.0.0
重啟伺服器
neo@netkiller:~$ sudo /etc/init.d/mysql reload * Reloading MySQL database server mysqld [ OK ]
建議使用reload,如果不起作用再用restart
登錄slave伺服器,測試主庫3306工作情況,如果看到下面相關信息表示工作正常。
# telnet 192.168.1.246 3306 Trying 192.168.1.246... Connected to 192.168.1.246. Escape character is '^]'. I 5.1.61-0ubuntu0.11.10.1-log1W<gs/*'#}p<u[J=5//:
創建賬戶並授予REPLICATION SLAVE權限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%.mydomain.com' IDENTIFIED BY 'slavepass'; mysql> FLUSH PRIVILEGES;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replication@'192.168.245.131' IDENTIFIED BY 'slavepass'
鎖表禁止寫入新數據
mysql> FLUSH TABLES WITH READ LOCK;
查看Master 工作狀態
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 106 | test | mysql | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
如果顯示下面內容表示,配置不正確
mysql> SHOW MASTER STATUS; Empty set (0.02 sec)
取得快照並記錄日誌名和偏移量後,可以在主伺服器上重新啟用寫活動
mysql> UNLOCK TABLES;
過程 1.2. Slave 設置步驟
配置my.cnf
從伺服器的ID必須與主伺服器的ID不相同,如果設置多個從伺服器,每個從伺服器必須有一個唯一的server-id值,必須與主伺服器的以及其它從伺服器的不相同。
# vim /etc/mysql/my.cnf [mysqld] server-id = 2
# service mysql restart mysql start/running, process 22893
指定 master 相關參數
在從伺服器上執行下面的語句,用你的系統的實際值替換選項值
mysql> CHANGE MASTER TO -> MASTER_HOST='master_host_name', -> MASTER_USER='replication_user_name', -> MASTER_PASSWORD='replication_password', -> MASTER_LOG_FILE='recorded_log_file_name', -> MASTER_LOG_POS=recorded_log_position;
CHANGE MASTER TO MASTER_HOST='192.168.245.129', MASTER_USER='replication', MASTER_PASSWORD='slavepass';
mysql> CHANGE MASTER TO MASTER_HOST='192.168.245.129', MASTER_USER='repl', MASTER_PASSWORD='slavepass'; Query OK, 0 rows affected (0.14 sec)
啟動從伺服器綫程
mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec)
SLAVE STATUS
mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.245.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 98 Relay_Master_Log_File: 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: 0 Relay_Log_Space: 98 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: NULL 1 row in set (0.00 sec)
登錄 master
複製進程的信息
SHOW PROCESSLIST語句可以提供在主伺服器上和從伺服器上發生的關於複製的信息
mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 62 User: replication Host: ken-hx409.local:36465 db: NULL Command: Binlog Dump Time: 679 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 2. row *************************** Id: 64 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: SHOW PROCESSLIST 2 rows in set (0.00 sec)
登錄從庫,查看複製綫程
mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 273 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: SHOW PROCESSLIST *************************** 2. row *************************** Id: 276 User: system user Host: db: NULL Command: Connect Time: 2 State: Waiting for master to send event Info: NULL *************************** 3. row *************************** Id: 277 User: system user Host: db: NULL Command: Connect Time: 2 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL 3 rows in set (0.00 sec)
如果沒有複製進程,請使用start slave;啟動
mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 273 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: SHOW PROCESSLIST 1 row in set (0.02 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
登錄 master
mysql> insert into foo(id,data) values(2,'Hello world!!!'); Query OK, 1 row affected (0.00 sec)
登錄 slave
mysql> select * from foo;
在master伺服器上插入一條記錄,你可以立刻在slave伺服器上看到變化。
資料庫已經存在的情況下怎麼遷移
Master 鎖表禁止寫入新數據
mysql> FLUSH TABLES WITH READ LOCK;
Slave 停止複製進程
mysql> stop slave;
備份Master資料庫
mysqldump yourdb | gzip > yourdb.sql.gz
恢復資料庫
如果使用mysqldump備份主伺服器的數據,將轉儲檔案裝載到從伺服器
# zcat yourdb.sql.gz | mysql -u root -p yourdb
啟動 Slave 複製進程
mysql> start slave;
解除 Master 表鎖定
mysql> UNLOCK TABLES;
MyIASM引擎可以採用下面方法
備份資料庫
# tar zcvf mysql-snapshot.tar.gz /var/lib/mysql/neo
複製給從資料庫
scp mysql-snapshot.tar.gz neo@192.168.245.131:/tmp
snapshot 恢復
$ tar zxvf mysql-snapshot.tar.gz $ cd /var/lib/mysql $ mv /tmp/var/lib/mysql/neo . $ sudo chown mysql.mysql -R neo
重新啟動Mysql
$ sudo /etc/init.d/mysql restart
有興趣可以看看mysqlhotcopy
複製帳號權限
grant replication slave on *.* to 'replication'@'192.168.1.%' identified by '000000';
主庫資料庫操作帳號權限
grant DELETE, INSERT, SELECT, UPDATE ON your_user.* to yourdb@'your_host' identified by 'password' with grant option;
從庫資料庫操作帳號權限
grant SELECT ON your_user.* to yourdb@'your_host' identified by 'password' with grant option;
從庫必須收回寫操作
my.cnf 檔案加入下面的內容
cp /etc/mysql/my.cnf /etc/mysql/my.cnf.old vim /etc/mysql/my.cnf [mysqld] server-id = 1 log-bin=/data/mysql/binlog/binlog binlog-do-db = test binlog-ignore-db=mysql log-slave-updates sync_binlog=1 auto_increment_offset=1 auto_increment_increment=2 replicate-do-db = test replicate-ignore-db = mysql,information_schema
創建複製權限
grant replication slave on *.* to 'replication'@'192.168.1.%' identified by '000000'; flush privileges;
mysql>flush tables with read lock; mysql> show master status\G *************************** 1. row *************************** File: binlog.000007 Position: 107 Binlog_Do_DB: test Binlog_Ignore_DB: mysql 1 row in set (0.00 sec)
創建複製權限
grant replication slave on *.* to 'replication'@'192.168.1.%' identified by '000000'; flush privileges;
my.cnf 檔案加入下面的內容
[mysqld] server-id = 2 log-bin = /data/mysql/binlog/binlog replicate-do-db = test replicate-ignore-db = mysql,information_schema binlog-do-db = test binlog-ignore-db=mysql log-slave-updates sync_binlog=1 auto_increment_offset=2 auto_increment_increment=2
B 與 A 配置檔案不同的兩處。
server-id = 2 auto_increment_offset=2
mysql> show master status\G *************************** 1. row *************************** File: binlog.000005 Position: 107 Binlog_Do_DB: test Binlog_Ignore_DB: mysql 1 row in set (0.00 sec)
Master A,首先鎖表為只讀狀態
mysqldump --databases test > /tmp/test.sql
Master B 創建一個與Master A同名的空資料庫,然後將備份檔案恢復到資料庫中
# mysql mysql> CREATE DATABASE test; mysql>\q # scp 192.168.1.1:/tmp/test.sql ./ # mysql -uroot -p test < /tmp/test.sql
master-A
mysql>change master to master_host='192.168.1.2', master_user='replication', master_password='000000', master_log_file='binlog.000005', master_log_pos=107;
master-B
mysql>change master to master_host='192.168.1.1', master_user='replication', master_password='000000', master_log_file='binlog.000007', master_log_pos=107;
mysql> SHOW VARIABLES LIKE "have_dynamic_loading"; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | have_dynamic_loading | YES | +----------------------+-------+ 1 row in set (0.00 sec) mysql>
master > INSTALL PLUGIN rpl_semi_sync_master SONAME ‘libsemisync_master.so’; slave-x > INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘libsemisync_slave.so’; master > SET GLOBAL rpl_semi_sync_master_enabled=1; slave-x > SET GLOBAL rpl_semi_sync_slave_enabled=1;
CREATE USER 'replication'@'192.168.1.%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'replication'@'192.168.1.%' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
master_heartbeat_period
STOP SLAVE; CHANGE MASTER TO master_heartbeat_period= milliseconds; START SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='root', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=107;
跳過出問題的MySQL實例
CHANGE MASTER TO MASTER_HOST=xxx.xxx.xxx.xxx IGNORE_SERVER_IDS=y
編輯my.cnf加入
# On Master [mysqld] rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000 # 1 second # On Slave [mysqld] rpl_semi_sync_slave_enabled=1