Master:192.168.1.101
Slave :192.168.1.102
单向同步（一）
- 进入Master启动MYSQL
- [root@localhost ~]# service mysqld start
- Starting MySQL [ OK ]
- 进入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>
- 为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)
- 创建更新日志文件
- mkdir /var/log/mysql
- chmod -R 775 /var/log/mysql
- chown -R mysql:mysql /var/log/mysql
- 配置/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日志文件名 - 本导入数据库
- 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
- 配置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用户配置 |
|
|
双向同步必免建值冲突 |