首页 > 代码库 > mysql主从同步配置方法和原理
mysql主从同步配置方法和原理
mysql主从复制试验环境:
1.CentOS5.8 x86_64位 采用最小化安装，系统经过了基本优化篇
2.mysql版本： 5.1.73-log ,同台服务器多实例,安装方法参考:mysql多实例安装方法
3.源码包存放位置：/home/oldboy/tools
4.源码包编译安装位置：/application/
一.修改master配置
1.修改my.cnf配置
vi /data/3306/my.cnf
egrep "log-bin|server-id" /data/3306/my.cnf #检查设置成如下
log-bin = /data/3306/mysql-bin
server-id = 1
#transaction_isolation = READ-COMMITTED
#要注释掉.不然插入数据,会报如下错误
mysql> insert into stu values (1, ‘txidc‘),(2,‘gaogao‘);
ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level ‘READ-COMMITTED‘ in InnoDB is not safe for binlog mode ‘STATEMENT‘
/data/3306/mysql start
2.mysql主库增加用于同步用户:
mysql> grant replication slave on *.* to rep@‘192.168.1.%‘ identified by ‘mygirl99‘
mysql> select user,host from mysql.user;
+------+-------------+
| user | host |
+------+-------------+
| root | 127.0.0.1 |
| rep | 192.168.1.% |
| root | localhost |
| root | ser200 |
+------+-------------+
4 rows in set (0.00 sec)
mysql> show grants for rep@‘192.168.1.%‘;
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.1.% |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO ‘rep‘@‘192.168.1.%‘ IDENTIFIED BY PASSWORD ‘*3561920064C790E42F7FBEBD0303ACC7C1EF855E‘ |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.主库锁表只读:此时业务会受影响.生产环境要考虑.要是停机选择流量最小时间
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘%timeout%‘
-> ;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 120 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 | #交互操时时间是28800秒
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 | #连接等待时间是28800秒,这2项时间要是过短.有可能会自动解锁表
+----------------------------+-------+
10 rows in set (0.00 sec)
4.查看主库的位置状态,从库里面是从此位置开始同步
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 832 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5.导出主库
[root@ser200 3306]# mysqldump -uroot -p -A -B -S /data/3306/mysql.sock |gzip >/server/bak/mysql_bak.$(date +%Y%m%d).sql.gz
Enter password:
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
[root@ser200 3306]# ll /server/bak/mysql_bak.20141029.sql.gz
-rw-r--r-- 1 root root 144960 Oct 29 15:48 /server/bak/mysql_bak.20141029.sql.gz
注意事项:
-A:备份所有库 -B,备份时增加use db 或者drop
如果数据库超过了100G+,备份时允许停机的话.可以直接打包数据文件迁移
6.在确认下主库的位置状态,确保导出过程,没有数据插入或者sql更新等操作
[root@ser200 3306]# mysql -uroot -pmybaby3306 -S /data/3306/mysql.sock -e ‘show master status‘;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 832 | | |
+------------------+----------+--------------+------------------+
7.解锁主库,恢复业务
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
二.从库slave操作
1.修改从库配置:
[root@ser200 3306]# cd /data/3307/
[root@ser200 3307]# vi my.cnf
[root@ser200 3307]# egrep "server-id|log-bin" my.cnf
#log-bin = /data/3307/mysql-bin
server-id = 2
2.导入数据库
[root@ser200 3307]# /data/3307/mysql start
[root@ser200 3307]# gunzip /server/bak/mysql_bak.20141029.sql.gz
[root@ser200 3307]# ll /server/bak/mysql_bak.20141029.sql
-rw-r--r-- 1 root root 527860 Oct 29 15:48 /server/bak/mysql_bak.20141029.sql
[root@ser200 3307]# mysql -uroot -pmybaby3307 -S /data/3307/mysql.sock < /server/bak/mysql_bak.20141029.sql
检查导入状态:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| test3306 |
+--------------------+
4 rows in set (0.00 sec)
3.登陆从库.配置从库同步参数
change master to \
master_host="192.168.1.200", \
master_user="rpe", \
master_port=3306, \
master_password="mygirl99", \
master_log_file="mysql-bin.000008", \
master_log_pos=832;
4.启动从库同步开关
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
报错
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.1.200
Master_User: rpe
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 832
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: No
Slave_SQL_Running: Yes
原因:
主库的用户名设置错误,造成连接不上.设置对应的就OK了
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.200
Master_User: rpe
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 1329
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 748
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 1329
Relay_Log_Space: 897
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
三.不停机实现主从库
1,原理:备份数据库记下bin-log的文件名和位置,下次导入时,从该位置开始同步就行了.
更多的mysql安装教程请点击
本文出自 “蜘蛛侠” 博客，请务必保留此出处http://txidc.blog.51cto.com/9138217/1569821
mysql主从同步配置方法和原理