MySQL主程序_mysql 主主

环境说明：

系统 主机名 IP mysql版本

Cenots 5.8 mysql-m1 192.168.3.173 5.0.95

Cenots 5.8 mysql-m2 192.168.3.178 5.0.95

1.首先安装mysql，为了方便，使用yum安装。(两台都要装)

[root@localhost ~]# yum install mysql mysql-server mysql-devel

2.启动mysql

2.1:先初始化mysql表 ------两台都要做

[root@mysql-m2 ~]# mysql_install_db

[root@mysql-m2 ~]# /etc/init.d/mysqld start

Starting mysqld: [ OK ]

3.创建同步的数据库和同步的用户 //两台都要做

3.1：创建数据库

mysql> create database test_db;

Query OK, 1 row affected (0.11 sec)

mysql> flush privileges; // 刷新系统权限表

Query OK, 0 rows affected (0.01 sec)

3.2：创建同步用的用户

mysql> insert into mysql.user(Host,User,Password) values('localhost','rsync',password='123456');

Query OK, 1 row affected, 3 warnings (0.02 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

3.3：授权用户能进行远程登陆

mysql> grant all privileges on test_db.* to 'rsync'@'%' identified by '123456';

Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

3.4：授权能打开复制进程

mysql> grant replication slave on *.* to 'rsync'@'%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

4.修改my.cnf文件 //两台都要做

在mysql上修改my.cnf文件

[root@mysql-m1 ~]# vim /etc/my.cnf

在mysqld中配置/添加：

binlog-do-db=test_db

binlog-ignore-db=mysql

replicate-do-db=test_db

replicate-ignore-db=mysql,information_schema,test

log-slave-updates

sync_binlog=1

auto_increment_offset=1

auto_increment_increment=2

slave-skip-errors=all

这里注意配置server-id的值，不同即可。

配置完成后重启mysql

/etc/init.d/mysqld restart

5.锁表 //两台都要做

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

6.查看这两台机器作为主服务器的状态

//mysql-m1:

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000009

Position: 98

Binlog_Do_DB: test_db

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

//mysql-m2:

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000002

Position: 98

Binlog_Do_DB: test_db

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

7.使用change master语句指定同步位置

//mysql-m1:

mysql> change master to master_host='192.168.3.178',

-> master_user='rsync',

-> master_password='123456',

-> master_log_file='mysql-bin.000002',

-> master_log_pos=98;

Query OK, 0 rows affected (0.03 sec)

//mysql-m2:

mysql> change master to master_host='192.168.3.173',

-> master_user='rsync',

-> master_password='123456',

-> master_log_file='mysql-bin.000009',

-> master_log_pos=98

-> ;

Query OK, 0 rows affected (0.08 sec)

8.打开表

//mysql-m1:

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

//mysql-m2:

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

9.分别启动从服务器线程

//mysql-m1:

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

//mysql-m2:

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

10.查看从服务器状态：

//mysql-m1:

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.3.178

Master_User: rsync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 235

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test_db

Replicate_Ignore_DB: mysql,information_schema,test

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 98

Relay_Log_Space: 235

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

//mysql-m2:

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.3.173

Master_User: rsync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 235

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test_db

Replicate_Ignore_DB: mysql,information_schema,test

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 98

Relay_Log_Space: 235

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

11.测试。

//mysql-m1:

创建表，并插入数据

mysql> use test_db;

Database changed

mysql> create table test_db(id int(3),name char(10),age char(8));

Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_db values(001,'baofeng','31');

Query OK, 1 row affected (0.02 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from test_db;

+------+---------+------+

| id | name | age |

+------+---------+------+

| 1 | baofeng | 31 |

+------+---------+------+

1 row in set (0.00 sec)

//mysql-m2查看:

mysql> use test_db;

Database changed

mysql> show tables;

+-------------------+

| Tables_in_test_db |

+-------------------+

| test_db |

+-------------------+

1 row in set (0.01 sec)

mysql> select * from test_db;

+------+---------+------+

| id | name | age |

+------+---------+------+

| 1 | baofeng | 31 |

+------+---------+------+

1 row in set (0.00 sec)

大家知道，mysql主从，从库进行操作是无法同步主库的。

现在在mysql-m2上进行删除操作:

mysql> delete from test_db;

Query OK, 1 row affected (0.00 sec)

mysql> show tables;

+-------------------+

| Tables_in_test_db |

+-------------------+

| test_db |

+-------------------+

1 row in set (0.00 sec)

//mysql-m1查看

mysql> select * from test_db;

Empty set (0.00 sec)

Import:这里有个很重要的点，如果mysql要做主主同步的库不为空，最好先停库导数据，然后锁表防止数据写入，在另外一个库上将数据导入

成功之后，然后解锁表，再进行slave复制同步操作。