MYSQL单双向同步

Master:192.168.1.101

Slave  :192.168.1.102

单向同步（一）

1. 进入Master启动MYSQL

   [root@localhost ~]# service mysqld start
   Starting MySQL                                             [  OK  ]

2.  进入Mysql命令行模行

   [root@localhost ~]# mysql -uroot -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 9 to server version: 5.0.22-log
   
   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   
   mysql>

3. 为slave用户添加同步专用权限

   mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO  'replication'@'192.168.1.102' identified by '123456';
   Query OK, 0 rows affected (0.01 sec)
   
   mysql> Flush privileges;
   Query OK, 0 rows affected (0.00 sec)

4. 创建更新日志文件

   mkdir /var/log/mysql
   chmod -R 775 /var/log/mysql
   chown -R mysql:mysql /var/log/mysql

5. 配置/etc/my.conf
    

   log-bin=mysql-bin	启用二制日志系统
   server-id=1	ID  标示为主，master_id必须为1到232–1之间的一个正整数值
   binlog-ignore-db=mysql,test	设置需要忽略同步数据库
   binlog-do-db=skydb or name.table	设置需要同步的数据库或同步的表，如果多个库，每个库增加一行.
   log-bin=/var/log/mysql/updatelog	设置log日志文件名

6. 本导入数据库

   service mysqld stop
   
   cd /var/lib/mysql  tar -jxf skydb.tar.bz2
   
   #如果需要冷copy 那么直接把数据完程复制到SLAVE上(可选）
   scp skydb.tar.bz2 root@192.168.1.102:/var/lib/mysql
   
   service mysqld start

7. 配置SLAVE /etc/my.cnf
     

   server-id = 2	Master 与Slave的不能相同
   master-host = 192.168.1.101	指定MASTER地址
   master-user = replication	同步用户名
   master-password = 123456	同步密码
   master-port = 3306	同步端口
   master-connect-retry=60	断开重新连接等待时间
   replicate-ignore-db=mysql	屏蔽需要同步数据库
   replicate-do-db= skydb relay-log=slave-relay-bin	同步数据库 生成日志文件

    

 

 
 

    

 

 

 

    8.解压MYSQL数据库（可选 ）

cd /var/lib/mysql
tar -jxf sky.tar.bz2

    9.配置SLAVE二进制数据库记录回归，达到同步

[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status; #这句是在MASTER服务器里面执行
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| updatelog.000004 |       98 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO MASTER_LOG_FILE='updatelog.000004',MASTER_LOG_POS=98;
Query OK, 0 rows affected (0.01 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

    10.如果出现mysql> slave start; ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

change master to master_host='192.168.1.101', master_user='replication', master_password='123456',master_log_file='updatelog.000001', master_log_pos=98;

     11.检查是配置成功

show slave status\G
Slave_IO_Running: YES
Slave_SQL_Running: YES

 

 

配置同步（二）

    1.修改原有SLAVE的my.cnf

server-id = 2	不要和主 ID 相同
master-host = 192.168.1.101	指定主服务器 IP 地址
master-user = replication	制定在主服务器上可以进行同步的用户名
master-password = 123456	密码
master-port = 3306	同步所用的端口
master-connect-retry=60	断点重新连接时间
replicate-ignore-db=mysql	屏蔽对 mysql 库的同步
replicate-do-db=skydb	同步数据库名称
relay-log=slave-relay-bin	启用日志
log-bin=/var/log/mysql/updatelog	设定生成log文件名
binlog-do-db=db1	设置同步数据库名
binlog-ignore-db=mysql	避免同步mysql用户配置
auto_increment_offset = 2   auto_increment_increment = 2	双向同步必免建值冲突