mysql DB复制同步(replication)

mysql  DB复制同步(replication)

系统环境:rhel6.0 x86-64
master:192.168.0.1
slave: 192.168.0.2

a(db1)---->b(db1)
b(db2)---->a(db2)
a-->b-->c-->d-->e

yum install mysql mysql-server -y

---->mysql单项复制
master server配置
####创建同步账户，并给予权限
mysql> grant replication slave,reload,super on *.* to test@'192.168.0.2' identified by 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

####配置master server 的/etc/my.cnf文件,添加黄色部分
vim /etc/my.cnf
[mysqld]
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=test
binlog-ignore-db=mysql

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

####启动mysql服务
/etc/init.d/mysqld restart

####检测
在master上用下面命令测试
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 | test         | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

####配置slave server 的/etc/my.cnf的文件
[mysqld]
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                                 #
master-host=192.168.0.1            #
master-user=test                      #
master-password=test                  #
master-port=3306               ##可缺省
master-connect-retry=60           ##可缺省
replicate-ignore-db=mysql        #
replicate-do-db=test         #

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

在slave上执行以下命令:
mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.0.1', master_user='test',
master_password='test', master_log_file='mysql-bin.000001',
master_log_pos=98;
Query OK, 0 rows affected (0.28 sec)
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

####开启服务
/etc/init.d/mysqld start

mysql> show slave status\G;
    ....
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
    ....

注：如果都是yes,表示从库的Slave_IO,Slave_SQL线程都正确开启.表明数据库正在同步
----> 若上面的Slave_IO,Slave_SQL状态为NO，则执行如下操作，再查看
cd /var/lib/mysql
rm -f master.info
rm -f mysqld-relay-bin.*
rm -f relay-log.info
/etc/init.d/mysqld stop
/etc/init.d/mysqld start

####测试，在master创建表，slave是否同步
---->master server
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> use test;
Database changed

mysql> show tables;
Empty set (0.00 sec)

mysql> create table westos (
    -> username varchar(25) not null,
    -> password varchar(25) not null);
Query OK, 0 rows affected (0.08 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| westos         |
+----------------+
1 row in set (0.00 sec)

---->slave server
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.01 sec)
mysql> use test;
Database changed

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| westos         |
+----------------+
1 row in set (0.00 sec)

mysql> desc westos;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(25) | NO   |     | NULL    |       |
| password | varchar(25) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

---->mysql双向复制
双向同步的mysql server是互为master and slave
####在原slave server操作
vim /etc/my.cnf添加黄色部分的3行
[mysqld]
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
binlog-do-db=test
binlog-ignore-db=mysql
master-host=192.168.0.1
master-user=test
master-password=test
replicate-ignore-db=mysql
replicate-do-db=test

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

注：添加上面3句后，slave server也就成为了可以同步的主机
重启mysql服务： /etc/init.d/mysqld restart

mysql> grant replication slave,reload,super on *.* to test@'192.168.0.1' identified by 'test';
Query OK, 0 rows affected (0.01 sec)
 mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

---->在master测试是否可以登录远程slave上面的数据库
[root@server1 ~]# mysql -utest -ptest -h 192.168.0.2
OK！！！！登录成功！

####在master server操作
vim /etc/my.cnf添加黄色字体部分
[mysqld]
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=test
binlog-ignore-db=mysql
master-host=192.168.0.4
master-user=test
master-password=test
replicate-do-db=test
replicate-ignore-db=mysql

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

重启mysql服务
/etc/init.d/mysqld restart

---->记录原slave上File  mysql-bin.000001 and 106
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 | test         | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

---->在原master server
mysql> slave stop;
mysql> change master to master_host='192.168.0.2',master_user='test',master_password='test',master_log_file='mysql-bin.000001',master_log_pos=106;
Query OK, 0 rows affected (0.22 sec)
mysql> slave start;
mysql > show slave status\G;
             ....
             Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes                       #IO读写正常
            Slave_SQL_Running: Yes                       #监听正常
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysq
         ....

####测试，在原slave server创建表，查看原master server是否同步
----> slave server 创建表
mysql> create table redhat(
    -> username varchar(25) not null,
    -> password varchar(25) not null);
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| redhat         |
| westos         |
+----------------+
2 rows in set (0.00 sec)

---->master查看是否同步
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| redhat         |
| westos         |
+----------------+
2 rows in set (0.00 sec)

mysql> desc redhat;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(25) | NO   |     | NULL    |       |
| password | varchar(25) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


GOOD LUCK!