mysql数据库同步
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;
5.1.2. Slave
过程 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 STATUSG *************************** 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)
5.1.3. Testing
-
登录 master
复制进程的信息
SHOW PROCESSLIST语句可以提供在主服务器上和从服务器上发生的关于复制的信息
mysql> SHOW PROCESSLISTG *************************** 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 PROCESSLISTG *************************** 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 PROCESSLISTG *************************** 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服务器上看到变化。
5.1.4. 将现有数据库迁移到主从结构数据库
数据库已经存在的情况下怎么迁移
-
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
5.1.5. 主从复制安全问题
复制帐号权限
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;
从库必须收回写操作
5.1.6. 主从不同步问题
执行下面语句
stop slave; set global sql_slave_skip_counter =1 ; start slave;
Seconds_Behind_Master 值从NULL变为大于等于0是表示已经同步
Seconds_Behind_Master: NULL Seconds_Behind_Master: 8893
5.2. Master Master(主主)
5.2.1. Master A
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 statusG *************************** 1. row *************************** File: binlog.000007 Position: 107 Binlog_Do_DB: test Binlog_Ignore_DB: mysql 1 row in set (0.00 sec)
5.2.2. Master B
创建复制权限
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 statusG *************************** 1. row *************************** File: binlog.000005 Position: 107 Binlog_Do_DB: test Binlog_Ignore_DB: mysql 1 row in set (0.00 sec)
5.2.3. 将Master A 数据库 同步到 Master B 两端数据库内容保持一致
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
5.2.4. Master A - B 同步两端数据库
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;
5.2.5. Master A 数据库解除只读权限
Master A 解锁
mysql> UNLOCK TABLES;
5.2.6. 查看主主的工作状态
分别在Master A与B 上运行
mysql>show slave statusG; Slave_IO_Running: Yes Slave_SQL_Running: Yes
5.3. 与复制有关的问题
5.3.1. expire-logs-days
缺省expire-logs-days为30天。这里设为7天，可根据自己情况调整。
[mysqld] expire-logs-days = 7
5.3.2. Semisynchronous Replication
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