mysq主从搭建

搭建双主结构的mysql

mysql 双主互备

目录  []

基本环境

system:Centos

mysql:5.0.45

ip:192.168.200.136 （a）

ip:192.168.200.162 （b）

创建用户service mysqld start mysqladmin -u root password xiwi

a）主机创建授权用户

[root@CentOS ~]# mysql -u root -pxiwi Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> grant replication slave,file on *.* to 'backup_251'@'192.168.200.162' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye

b）主机创建授权用户

[root@CentOS ~]# mysql -u root -pxiwi Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> grant replication slave,file on *.* to 'backup_252'@'192.168.200.136' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye修改mysql配置

a）

[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 user = mysql log-bin=mysql-bin server-id= 1 binlog-do-db=test binlog-ignore-db=mysql replicate-do-db=test replicate-ignore-db=mysql log-slave-updates slave-skip-errors=all sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

b）

[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords= 1 user = mysql log-bin= mysql-bin server-id= 2 binlog-do-db=test binlog-ignore-db=mysql replicate-do-db=test replicate-ignore-db=mysql log-slave-updates slave-skip-errors=all sync_binlog=1 auto_increment_increment=2 auto_increment_offset=2 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

简述下配置的作用。

• log-slave-updates 表示如果一个MASTER 挂掉的话，另外一个马上接管。
• auto_increment_offset=1 指的是服务器频繁的刷新日志。这个保证了在其中一台挂掉的话，日志刷新到另外一台。从而保证了数据的同步 。
• auto_increment_offset=1 auto_increment_increment=2 这样A的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID了。

auto_increment字段在不同的服务器之间绝对不会重复，所以Master-Master结构就没有任何问题了。当然，你还可以使用3 台，4台，或者N台服务器，

只要保证auto_increment_increment=N再设置一下auto_increment_offset为适当的初始值就可以了，那样，我们的MySQL可以同时有几十台主服务器，

而不会出现自增长ID重复。

配置双主结构

a）

mysql> flush tables with read lock\G Query OK, 0 rows affected (0.00 sec) mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000002 Position: 98 Binlog_Do_DB: test Binlog_Ignore_DB: mysql 1 row in set (0.00 sec)

b）

mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000003 Position: 98 Binlog_Do_DB: test Binlog_Ignore_DB: mysql 1 row in set (0.00 sec)

然后备份自己的数据，保持两个机器的数据一致。

在各自机器上执行CHANGE MASTER TO命令。

a）

mysql> change master to -> master_host='192.168.200.162', -> master_user='backup_252', -> master_password='123456', -> master_log_file='mysql-bin.000003', -> master_log_pos=98; Query OK, 0 rows affected (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)

b)

mysql> change master to -> master_host='192.168.200.136', -> master_user='backup_251', -> master_password='123456', -> master_log_file='mysql-bin.000002', -> master_log_pos=98; Query OK, 0 rows affected (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)

a）

mysql> show processlist\G *************************** 1. row *************************** Id: 3 User: system user Host: db: NULL Command: Connect Time: 752 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 4 User: system user Host: db: NULL Command: Connect Time: 724 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL *************************** 3. row *************************** Id: 5 User: backup_251 Host: 192.168.200.162:45523 db: NULL Command: Binlog Dump Time: 516 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 4. row *************************** Id: 6 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist 4 rows in set (0.00 sec)

b）

mysql> show processlist\G *************************** 1. row *************************** Id: 2 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist *************************** 2. row *************************** Id: 3 User: backup_252 Host: 192.168.200.136:49915 db: NULL Command: Binlog Dump Time: 216 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 3. row *************************** Id: 4 User: system user Host: db: NULL Command: Connect Time: 35 State: Waiting for master to send event Info: NULL *************************** 4. row *************************** Id: 5 User: system user Host: db: NULL Command: Connect Time: 7 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL 4 rows in set (0.00 sec)

至此双master的mysql已经搭建完毕

验证效果

• 创建一个测试表。

mysql> use test mysql> create table test(id int auto_increment not null,username VARCHAR(25), country VARCHAR(10),primary key(id));

• 自动插入脚本

#!/usr/bin/perl -w #this script is just a demo for connect mysql and insert some data use strict; use DBI; my $username = "root"; #user for mysql to login my $password = "xiwi"; #password my $data_source = 'DBI:mysql:database=test;host=localhost'; #you shoud only change congcong to you own database name and change local host to you mysql host my $dbh = DBI->connect($data_source, $username, $password) or die $DBI::errstr; foreach my $id(1..100) { $dbh->do("insert into test(username) values($id);"); #the sql you done } $dbh->disconnect;

• 将自动插入脚本同时运行在两台mysql机器上。验证效果。

mysql> use test mysql> select * from test ;