mysql db 复制_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!