【www.bbyears.com--mysql教程】
假设两台MySql的IP分别为：
MySql-Master 192.168.0.1
MySql-Slave 192.168.0.2
1.配置MySql-Master
~# vi /etc/mysql/my.cn
server-id = 238 #唯一id，一般用ip的最后一段
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_ignore_db = mysql #设置不用同步的表
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
auto-increment-increment = 2 #因为我们有两台机子，这里插入数据的自增长设为2
auto-increment-offset = 1 #这个库中每插入一条自增长的偏移量为1
2.重启一下mysql
~# service mysql restart
3.设置一个复制master的mysql用户给slave
~# mysql -uroot -p"welcome"
mysql> CREATE USER rep1@"192.168.0.2" IDENTIFIED BY "welcome";
mysql> GRANT REPLICATION SLAVE ON *.* TO rep1@"192.168.0.1";
4.查看一下master的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 435 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
5.配置MySql-Slave
~# vim /etc/mysql/my.cnf
server-id = 129
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
replicate-ignore-db = mysql #不用复制的表
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
log-slave-updates = ON
relay_log = mysqld-relay-bin
6.重启一下mysql
~# service mysql restart
7.设置一下master，并查看一下slave状态，确认一下是否OK
mysql> change master to
-> master_host="192.168.0.1",
-> master_user="rep1",
-> master_password="welcome",
-> master_log_file="mysql-bin.000002",
-> master_log_pos=329;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
8.创建一个mysql用户给master来复制slave
mysql> CREATE USER rep2@"192.168.1.1" IDENTIFIED BY "welcome";
mysql> GRANT REPLICATION SLAVE ON *.* TO rep2@"rep2@"192.168.1.1";
9.配置salve的自增长和偏移
~# vim /etc/mysql/my.cnf
read-only = 0
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
auto-increment-increment = 2
auto-increment-offset = 2
~# service mysql restart
10.设置master复制slave，其实就是把slave当真master的主来复制
~# vim /etc/mysql/my.cnf
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = information_schema
relay_log = mysqld-relay-bin
log-slave-updates = ON
mysql> change master to
-> master_host="192.168.0.2",
-> master_user="rep2",
-> master_password="welcome",
-> master_log_file="mysql-bin.000008",
-> master_log_pos=433;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
~# service mysql restart
OK,可以测试一下导入数据库，添删改查一下试试。