MySQL主从同步配置详解

环境：

系统：CentOS Linux release 6.0 .x86_64

软件： mysql.5.1.X

Master IP 192.168.200.1

Slave IP 192.168.200.2

mysql安装请参照 mysql单实例安装  

MYSQL主从同步的作用

(1) 数据分布

(2) 负载平衡(load balancing)

(3) 备份

(4) 高可用性(high availability)和容错

MYSQL主从同步的原理

关于MYSQL的主从同步，最主要的是要了解MYSQL的主从同步是如何工作的也即主从同步的原理，通过下图能很明白的指导其工作的过程：

配置：

一、Master的配置

1、在/etc/my.cnf里添加如下内容：

双击代码全选

1 2 3 4 5 6 7 8 9

server-id = 1   sync_binlog=1   auto_increment_increment=2   auto_increment_offset=1   log-slave-updates   log-bin=mysql-bin    binlog-ignore-db=mysql  character- set -server=utf8  init_connect= 'SET NAMES utf8'

设定了默认字符集为utf8，可以按实际情况取舍这段配置。

 2、在Mster 上建立同步账号

双击代码全选

1 2	GRANT REPLICATION SLAVE,FILE ON *.* TO 'backup' @ '192.168.200.2' IDENTIFIED BY '123456' ;   FLUSH PRIVILEGES;

二、Slave 的配置，在/etc/my.cnf里添加如下内容：

双击代码全选

1 2 3 4 5 6 7 8 9 10 11

server-id = 2   sync_binlog=1   auto_increment_increment=1   auto_increment_offset=1   log-slave-updates   log-bin=mysql-bin   binlog-ignore-db=mysql   master-host = 192.168.200.2   master-user = backup   master-password = 123456   master-port = 3306

三、重启Master和slaved服务器mysql服务。

四、Slave服务器从主服务器读取数据，并开启同步

1、

双击代码全选

1 2	FLUSH TABLES WITH READ LOCK; #首先执行这个对主服务器进行加锁   LOAD DATA FROM MASTER  #语句的话，必须授予全局的 FILE 和 SELECT 权限，仅针对MyISAM引擎，对InnoDB表无用。

2、 如果数据是InnoDB的，需要在服务器上将表结构和数据分开导出，然后拷贝至Slave服务器。 导出整个数据库结构和数据：mysqldump -u用户名 -p密码 -h主机 database > filename.sql 导出某个表的数据和结构：mysqldump -u用户名 -p密码 -h主机 database table > filename.sql 只导出数据库中表的数据：mysqldump -u用户名 -p密码 -h主机 -T database table > filename.sql 只导出数据库中表的结构：mysqldump -u用户名 -p密码 -h主机 -d database table > filename.sql

3、在Master服务器上查看master的状态

双击代码全选

1 2 3 4 5 6 7

mysql> show master status;   +------------------+----------+--------------+------------------+   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |   +------------------+----------+--------------+------------------+   | mysql-bin.000011 |      106 | db_cngamer   |   mysql               |   +------------------+----------+--------------+------------------+   1 row in set (0.00 sec)

可以看到日志文件是：mysql-bin.000011，同步点是106

4、在Slave服务器上执行如下语句

双击代码全选

1 2 3 4 5

mysql> CHANGE MASTER TO MASTER_HOST= '192.168.11.244' ,MASTER_USER= 'backup' ,MASTER_PASSWORD= '123456' ,MASTER_LOG_FILE= 'mysql-bin.000011' ,MASTER_LOG_POS=106;    Query OK, 0 rows affected (0.01 sec)           mysql> start slave;   Query OK, 0 rows affected (0.00 sec)

4、在slave上查看同步状态

双击代码全选

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

mysql> show slave statusG    *************************** 1. row ***************************                 Slave_IO_State: Queueing master event to the relay log                    Master_Host: 192.168.200.1                    Master_User: backup                    Master_Port: 3306                  Connect_Retry: 60                Master_Log_File: mysql-bin.000011            Read_Master_Log_Pos: 106                 Relay_Log_File: mysqld-relay-bin.000010                  Relay_Log_Pos: 106          Relay_Master_Log_File: mysql-bin.000011               Slave_IO_Running: Yes              Slave_SQL_Running: Yes                Replicate_Do_DB: typecho            Replicate_Ignore_DB: mysql,test             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: 106                Relay_Log_Space: 106                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)

可以看到，Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes"，这表明 Slave 的 I/O 和 SQL 线程都在正常运行。

5、配置完成

本文出自 “平凡的日子” 博客，请务必保留此出处