MySQL数据库的主从复制详解

来源：互联网发布：oanda 数据接口编辑：程序博客网时间：2024/05/22 04:25

配置机器：

master server 192.168.0.40

slave server 192.168.0.208 ，此机器上MySQL版本为5.5.29

接下来开始配置，首先是master server

一、master server

1. 配置my.cnf
vim /etc/my.cnf

在[mysqld]中添加
server-id =1 //为1表示为Master,不需要更改

log-bin=mysql-bin //日志为二进制，无需更改

binlog_format=mixed //二进制日志的格式，无需更改

binlog-do-db=chinaetax_2_0 //要同步的数据库(可以有多个)

binlog-do-db=cms_2_0 //要同步的数据库

binlog-ignore-db=mysql,test,information_schema //不需要同步的数据库名，多个数据库中间用逗号(,)隔开

然后把innodb前面的#去掉，结果如下

innodb_data_home_dir = /usr/local/mysql/data/ //innodb的表空间位置

innodb_data_file_path = ibdata1:50M:autoextend //表空间的名字，开始50M

innodb_log_group_home_dir = /usr/local/mysql/data/

innodb_buffer_pool_size = 256M //为系统内存的50-80%

innodb_additional_mem_pool_size = 20M

innodb_log_file_size = 64M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

然后重启数据库,让配置文件生效

#service mysqld restart
或者先停止mysql服务，再开启服务
#service mysqld stop
#/etc/init.d/mysqld start

2、在master server上添加授权账号，用于slave server对master server的访问

格式:GRANT REPLICATION SLAVE ON *.* TO '帐号'@'从服务器IP或主机名' IDENTIFIED BY '密码';

登录mysql服务器

#mysql -u root -p
授权

mysql>grant replication slave on *.* to repluser@'192.168.0.208' identified by '123456';
刷新权限列表

mysql>flush privileges;

3、备份master server上数据库(两种方式)
第一种：

mysql> flush tables with read lock; //读锁

同时要记录下mysql-bin.000006和1135164

mysql> show master status;

+----------------------------+---------- +--------------------------- + --------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------------+---------- +--------------------------- + --------------------------------------------------------------------+
| mysql-bin.000006 |1135164 | chinaetax,cms_2_0 | mysql,information_schema,performance_schema,|
+---------------------------- +---------- +--------------------------- + --------------------------------------------------------------------+
1 row in set (0.00 sec)

1 row in set (0.01 sec)

取得快照并记录日志名和偏移量

开启另一个终端对主服务器数据目录做备份。

#cd /usr/local/mysql/data

#tar -zcvf backup.tar.gz www blog

此时在主库解开table的锁定

mysql> unlock tables;

第二种：
#mysqldump -uroot -p -l -F databasename > 路径

二、slave server

1. vim /etc/my.cnf
log-bin=mysql-bin // 开启bin-log日志
binlog_format=mixed

server-id = 2 //server-id不能和master server相同，即不能为1

master-host = 192.168.0.40 //以下5个配置项在mysql5.5版本以后废弃

master-user = repluser

master-password = 123456

master-port = 3306

master-connect-retry=60

replicate-do-db=chinaetax_2_0 //告诉slave只做www数据库的更新

replicate-do-db=cms_2_0 //告诉slave只做blog数据库的更新

一开始，我也是按照上面这样配置的，可是，服务器总是启动不了，报这样的错误，不知大家有没有遇到过，
The server quit without updating PID file，
后来，查看mysql错误日志，是这样写的，

最后在官网上，看到了这个

终于明白了

！！！注意：从MySQL5.5版本开始，master-host,master-user,master-password，等变量已移除，所以才会出现上面的错误，可以在登录mysql服务器控制台后，用change master to命令动态改变配置
所以，如果mysql版本是5.5以上，这一步只需配置前三项和后两项即可，然后重启服务器
#service mysqld start

2、把从主数据库服务器备份出来的数据库导入到从服务器中

先用scp把主服务器上的backup.tar.gz拷贝过来,解压到/usr/local/mysql/data目录
   #scp backup.tar.gz 192.168.0.208:/usr/local/mysql/data
   #cd /usr/local/mysql/data
   #tar -xzvf backup.tar.gz

如果是用mysqldump备份的数据库，在208服务器上直接用mysql命令导入数据库即可，过程略过

3、对于mysql 5.5以上，这一步必须配置

停止slave服务，设置主服务器的各种参数

mysql>slave stop;

然后敲入以下代码,一行一行的复制

mysql>change master to

MASTER_HOST='192.168.0.40',

MASTER_USER='repluser',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000006',

MASTER_LOG_POS=1135164;

mysql> slave start;

OK，以上配置结束。

敲入以下命令，查看是否配置成功。

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.40

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos:1135164

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 617

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: chinaetax_2_0,cms_2_0

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: 1271

Relay_Log_Space: 773

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.01 sec)

在以上各项中，保证以下两项为Yes，说明成功。

Slave_IO_Running：

是否要从 Master Server 复制 Binary Log日志，必须为 Yes。

Slave_SQL_Running：

是否要执行从 Master Server复制过来的 Binary Log日志，必须为 Yes。

然后在master上添加数据

再在slave server上查询，发现数据斗能更新过来，成功。