目前有个需求,希望把公司的多个mysql数据库备份,有一台服务器存储比较大,计划把其他的mysql备份到这上面,一开始计划使用dump回传,后来发现有些库数据比较大,因此使用master-slave,即多主一丛。
1 先在备份服务器安装mysql,开启mysqlmulti模式,配置my.cnf
[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
user = ‘your_user’
password=‘your_password’
log=/home/mysql/log/multi.log
[client]
#password = your_password
[mysqld1]
#主库1
port = 3306
socket=/tmp/mysql1.sock
pid-file=/home/mysql/mysqldata1/mysql1.pid
datadir=/home/mysql/mysqldata1
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
#(注意linux下mysql安装完后是默认:表名区分大小写,列名不区分大小写; 0:区分大小写,1:不区分大小写)
lower_case_table_names=1
max_connections=1000
server-id = 1
#要备份的库
replicate-do-db = mysql1
slave_skip_errors= 1158 1159 1008 1007 1062 1452
#slave_skip_errors= all
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld2]
#主库2
port = 3307
socket=/tmp/mysql2.sock
pid-file=/home/mysql/mysqldata2/mysql2.pid
datadir=/home/mysql/mysqldata2
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
#(注意linux下mysql安装完后是默认:表名区分大小写,列名不区分大小写; 0:区分大小写,1:不区分大小写)
lower_case_table_names=1
max_connections=1000
server-id = 2
#要备份的库
replicate-do-db = mysql2
slave_skip_errors=1158 1159 1008 1007 1062 1452
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size= 268435456
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
2 启动关闭数据库,my.cnf配置好,启动时会自动创建文件和库文件,注意账号权限。
mysqld_multi start 1
mysqld_multi stop 1
mysqld_multi start 2
mysqld_multi stop 2
3 设置各数据的密码
/usr/bin/mysqladmin -u root password 'you_password' -S /tmp/mysql1.sock
/usr/bin/mysqladmin -u root password 'you_password' -S /tmp/mysql2.sock
测试能关闭启动数据库,密码能进入
4 各主库设置同步账号,注意授权和同步库,我这里是全部同步,偷懒全部授权了莫学。
grant all privileges on *.* to 'your_user'@'%' identified by 'you_password';
5主库要开log-bin,即在主库my.cnf增加重启,开过这步可省略
server-id=219 #主库和备库id不能重复
log-bin=mysql-bin
expire_logs_days=8
6 导出备份库,具体参数含义百度吧,我这里就不详细介绍了
mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 mysql1 > mysql1.sql
mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 mysql2 > mysql2.sql
查看主库导出的事务位置,即找到CHANGE MASTER这行
head -22 mysql1.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120;
head -22 mysql2.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=147774;
7 将导出文件传到备库并导入,导入前要创建数据库名
mysql -S /tmp/mysql1.sock -uroot -p mysql1 < /root/mysql1.sql
mysql -S /tmp/mysql1.sock -uroot -p mysql2 < /root/mysql2.sql
8 导入成功后,在备库启用同步,注意MASTER_LOG_FILE和 MASTER_LOG_POS和第6步有关系
change master to master_user='you_user',master_password='your_password',master_host='your_host',master_port=your_port,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120;
change master to master_user='you_user',master_password='your_password',master_host='your_host',master_port=your_port,
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=147774;
start slave;
show slave status\G; 查看同步状态
至此两主一丛基本完成,中间若出现问题根据问题报错解决,mysql相对来说上手还是比较简单,网上各种问题的介绍也比较多,大家可以参考。