mysql 主从同步

（前提：主从服务器能够互通，主IP:192.168.2.71，从IP:192.168.2.73，主从服务器mysql-server版本尽量保持一致）

1、先设置主服务器，配置文件如下：

[mysqld]
default-character-set=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=1
log-bin=mysql-bin
binlog-do-db=nsaddp
binlog-ignore-db=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=utf8

2、主服务器创建slave用户，用于同步，然后重启数据库

mysql>grant replication slave on *.* to ‘slave’@’192.168.2.73’ identified by ‘123456’;
mysql>flush privileges;

3、查看主的状态，记住日志文件名和position信息，后面从服务器需要使用

mysql>show master status;

如得到如下相关信息，则表示成功

+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 |     3260 |              | nsaddp |
+-------------------+----------+--------------+-------------------------------+

4、主服务器备份数据（先锁住表，flush tables with read lock;并上传到从服务器）

#mysqldump -u root -p nsaddp > /home/mysql.bak.sql
#scp /home/mysql.bak.sql root@192.168.2.73:/home/

5、恢复从服务器的数据当（从服务器恢复之后解锁表，unlock tables）

mysql>create database nsaddp;
#mysqldump -u root -p nsaddp < /home/mysql.bak.sql;
mysql>use nsaddp;
mysql>source /home/mysql.bak.sql;

6、从服务器配置

[mysqld]
default-character-set=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=2
log-bin=mysql-bin
master-host=192.168.2.71
master-user=slave
master-password=123456
master-port=3306

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=utf8

6、重启从服务器，并且查看状态

#/etc/init.d/mysqld restart
mysql>show slave status\G;

若得到如下相关信息，则表示成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes