实现mysql多主一从备份

说明:
本篇文章实现xm8开启多个实例,同时做为xm7,xm9的mysql从库,实时同步xm7的qq_data库和xm9的qq_data库。其中qq_data为innodb引擎,innodb引擎数据库数据都保存在ibdata1中。因为xm7和xm9的myql库都为qq_data,且都是innodb引擎的,所以replicate主从过来后会交叉覆盖,从而导致从服务器数据混乱。因此我们需要为每个实例配置配置不同的数据库存储目录。

环境:

mysql主1 xm7  192.168.1.7  /home/mysql/data xm7的qq_data数据库目录
mysql从  xm8  192.168.1.8   /home/mysql/data1存储xm7的qq_data;/home/mysql/data2存储xm9的qq_data
mysql主2 xm9  192.168.1.9  /home/mysql/data xm9的qq_data数据库目录

 
实现:
1.修改xm7,xm9的/etc/my.cnf,开启binlog,开启主从

修改xm7的/etc/my.cnf文件,设置需要同步的数据库qq_data
# vim /etc/my.cnf //在log-bin=mysql-bin.log后面添加以下内容
expire_logs_days=10
binlog_do_db=qq_data
binlog_ignore_db=mysql

修改xm9的/etc/my.cnf文件,设置需要同步的数据库qq_data
# vim /etc/my.cnf //在log-bin=mysql-bin.log后面添加以下内容
expire_logs_days=10
binlog_do_db=qq_data
binlog_ignore_db=mysql
修改完成后分别重启xm7,xm9的mysql,使配置生产。

 
2.修改xm8的/etc/my.cnf,设置mysqld_multi与mysqld多实例,针对实例设置binlog,开启主从
从服务器xm8配置文件/etc/my.cnf,主要有两部分[mysqld_multi]和[mysqld]模块。[mysqld2]就是[mysqld1]的一个拷贝,区别于[mysqld1]的端口和文件位置。注意server-id不要重复

# grep -v '^#' /etc/my.cnf  //以下为/etc/my.cnf完整配置
[client]

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
password = 123456

[mysqld1]
port            = 3306
socket          = /tmp/mysql1.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-error = /home/mysql/logs/mysql1_error.log
pid-file = /usr/local/mysql/mysql1.pid
datadir = /home/mysql/data1

log-bin=mysql1-bin
binlog_format=mixed

server-id       = 70
master-host     = 192.168.45.7
master-user     = slave
master-password = 123456
master-port     = 3306
master-connect-retry    = 5
replicate-do-db = qq_data
replicate-ignore-db     = mysql
relay-log=relay-bin1
relay-log-index=relay-bin1.index
relay-log-info-file=relay-log1.info

innodb_data_home_dir = /home/mysql/data1/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/mysql/data1/
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqld2]
port            = 3307
socket          = /tmp/mysql2.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-error = /home/mysql/logs/mysql2_error.log
pid-file = /usr/local/mysql/mysql2.pid
datadir = /home/mysql/data2

log-bin=mysql2-bin
binlog_format=mixed

server-id       = 90
master-host     = 192.168.45.9
master-user     = slave
master-password = 123456
master-port     = 3306
master-connect-retry    = 5
replicate-do-db = qq_data
replicate-ignore-db     = mysql
relay-log=relay-bin2
relay-log-index=relay-bin2.index
relay-log-info-file=relay-log2.info

innodb_data_home_dir = /home/mysql/data2/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/mysql/data2/
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

 
3.xm7,xm9给予xm8 replication,slave权限

xm7给予xm8 replication,slave的权限,账号为slave,密码123456
# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql.sock
> grant replication slave on *.* to slave@192.168.1.8 identified by '123456';
> quit
同样xm9给予xm8 replication,slave的权限,账号为slave,密码123456
# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql.sock
> grant replication slave on *.* to slave@192.168.1.8 identified by '123456';
> quit

 
4.xm8上新建两个/home/mysql/data1,/home/mysql/data2,分别初始化mysql数据库目录data1,data2

# /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/home/mysql/data1/ --user=mysql
# /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/home/mysql/data2/ --user=mysql

 
5.分别停止xm7,xm8的数据库,当然锁定qq_data也可以

# /etc/init.d/mysqld stop  //在xm7,xm9上分别运行,停止数据库

 
6.在xm8上rsync同步xm7,xm9的数据库文件qq_data到xm8上

# rsync -av root@192.168.45.7:/home/mysql/data/ibdata1 /home/mysql/data1/  //rsync xm7的qq_data数据库数据到xm8上
# rsync -av root@192.168.45.7:/home/mysql/data/qq_data /home/mysql/data1/  //rsync xm7的qq_data数据库表结构到xm8上
# rsync -av root@192.168.45.9:/home/mysql/data/ibdata1 /home/mysql/data2/
# rsync -av root@192.168.45.9:/home/mysql/data/qq_data /home/mysql/data2/

qq_data为innodb引擎,innodb引擎数据库数据都保存在ibdata1中

7.开启xm7,xm9的mysql,xm7,xm9分别运行以下命令

# /etc/init.d/mysql start

 
8.启动/停止xm8 mysql实例

# /usr/local/mysql/bin/mysqld_multi start 1,2
# /usr/local/mysql/bin/mysqld_multi stop 1,2

查看每个实例是否都起来了
# ps aux|grep mysql|grep -v grep
我们可以看到四个mysql进程,每个实例分别有两个进程

分别访问mysql实例1,实例2
# /usr/local/mysql/bin/mysql -uroot -p123456 -P3306 -S /tmp/mysql1.sock
# /usr/local/mysql/bin/mysql -uroot -p123456 -P3307 -S /tmp/mysql2.sock

 
9.查看主从数据同步情况
在xm7上查看

# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql.sock
> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      371 | qq_data      | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 
在xm9上查看

# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql.sock
> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      537 | qq_data      | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 
在xm8从数据库上查看,因为xm8上运行两个实例,实例1对应xm7 qq_data,实例2对应xm9的qq_data,因此需要独立运行查看

# /usr/local/mysql/bin/mysql -uroot -p123456 -P3306 -S /tmp/mysql1.sock  //实例1
> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.45.7
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 371
               Relay_Log_File: relay-bin1.000002
                Relay_Log_Pos: 516
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: qq_data
          Replicate_Ignore_DB: mysql

# /usr/local/mysql/bin/mysql -uroot -p123456 -P3307 -S /tmp/mysql2.sock  //实例2
> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.45.9
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 537
               Relay_Log_File: relay-bin2.000004
                Relay_Log_Pos: 682
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: qq_data
          Replicate_Ignore_DB: mysql

可以看到Slave_IO_State为Waiting for master to send event,Slave_IO_Running,Slave_SQL_Running都是Yes。而且Master_Log_File与Read_Master_Log_Pos都分别与xm7,xm9对应。说明主从正确运行。

测试:

在xm7上创建users表
# /usr/local/mysql/bin/mysql -uroot -p123456
> use qq_data
> CREATE TABLE `users` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`website` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

在xm9上创建users_log表
# /usr/local/mysql/bin/mysql -uroot -p123456
> user qq_data
> CREATE TABLE `users_log` (
`user_log_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`info` varchar(200) DEFAULT NULL,
`uid` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`user_log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

然后分别登陆xm8的两个实例(实例1对应xm7 qq_data,实例2对应xm9的qq_data),就可以看到xm7 qq_data库上新建的users表,xm9 qq_data新建的users_log表。

附录:
mysql的编译安装可以参考:mysql 5.1 编译安装
mysql的主从配置可以参考:mysql主从同步备份

“实现mysql多主一从备份”的2个回复

发表评论

邮箱地址不会被公开。 必填项已用*标注