innobackupex 备份数据搭建 MySQL Slave

简介:

数据量比较大时,使用 innobackupex 备份数据新增 MySQL Slave 节点。

安装 innobackupex 工具,我这里写过一次:http://www.cnblogs.com/wangxiaoqiangs/p/5961413.html

场景:

  A -> B
-> C
-> D -> E

一、增加节点 C

# 由于有从库 B ,所以我们去 B 上面执行备份

shell > innobackupex --user=xx --password=xx --slave-info --safe-slave-backup --no-timestamp alldatabase

# 保存 B 的 Slave 信息,因为新增的节点 C 与 B 同级

shell > scp -r alldatabase mysql-nodeC:/root

# 将备份数据拷贝到新节点 C 上,如果节点 C 在外网,建议先压缩一下

# 登录节点 C

shell > vim /etc/my.cnf  # 准备配置文件

[client]
port =
socket = /tmp/mysql.sock [mysqld]
socket = /tmp/mysql.sock
datadir = /data/mysql_data
log-error = error.log
skip-name-resolve
skip-external-locking
default-storage-engine = MyISAM
key_buffer_size = 3072M
sort_buffer_size = 256M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
table_open_cache =
table_definition_cache =
thread_cache_size =
back_log =
connect_timeout =
max_connections =
wait_timeout =
interactive_timeout =
max_allowed_packet = 1024M slow_query_log = ON
long_query_time =
slow-query-log-file = slow.log query_cache_type =
query_cache_size =
query_cache_limit = tmp_table_size = 128M
max_heap_table_size = 128M innodb_file_per_table =
innodb_buffer_pool_size = 1024M read-only =
relay-log = mysql-relay-bin
expire_logs_days =
server-id =

# 注意:server-id 必须全局唯一
# 如果新增的是节点 D,那么需要将后4行替换为如下7行

relay-log = mysql-relay-bin
relay_log_purge = ON
log-slave-updates =
log-bin = mysql-bin
binlog_format = mixed
expire_logs_days =
server-id =

# 因为节点 D 不仅是 Master A 的 Slave,同时也是节点 E 的 Master

shell > innobackupex --apply-log alldatabase  # 在备份目录上应用日志
:: completed OK! shell > innobackupex --copy-back alldatabase # 将备份文件拷贝到 my.cnf 中 datadir 指定目录
:: completed OK! shell > chown -R mysql.mysql /data/mysql_data shell > /etc/init.d/mysql.server start shell > cat /data/mysql_data/xtrabackup_slave_info # 记录 master_log_file 和 master_log_pos 的值
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.003742', MASTER_LOG_POS= shell > mysql -uxx -pxx -A mysql> stop slave;
mysql> reset slave all;
mysql> change master to master_host='x.x.x.x',master_user='xx',master_password='xx',master_log_file='mysql-bin.003742',master_log_pos=;
mysql> start slave;
mysql> show slave status\G Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> quit

# master_host='A'
# MySQL Slave 节点 C 搭建完成

二、新增节点 E

# 由于节点 D 后面没有从节点,所以备份要在节点 D 上执行

shell > innobackupex --user=xx --password=xx --no-timestamp alldatabase

# 我们要的是节点 D 的信息,所以不需要保存 Slave 信息( Slave 信息为跟节点 A 同步信息 )

shell > scp -r alldatabase mysql-nodeE:/root

# 将备份数据拷贝到新节点 E 上,如果节点 E 在外网,建议先压缩一下

shell > mysql -uxx -pxx -A  # 跟上面不一样的地方,需要新建立一个授权用户

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

# 登录节点 E

shell > vim /etc/my.cnf  # 准备配置文件

[client]
port =
socket = /tmp/mysql.sock [mysqld]
socket = /tmp/mysql.sock
datadir = /data/mysql_data
log-error = error.log
skip-name-resolve
skip-external-locking
default-storage-engine = MyISAM
key_buffer_size = 3072M
sort_buffer_size = 256M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
table_open_cache =
table_definition_cache =
thread_cache_size =
back_log =
connect_timeout =
max_connections =
wait_timeout =
interactive_timeout =
max_allowed_packet = 1024M slow_query_log = ON
long_query_time =
slow-query-log-file = slow.log query_cache_type =
query_cache_size =
query_cache_limit = tmp_table_size = 128M
max_heap_table_size = 128M innodb_file_per_table =
innodb_buffer_pool_size = 1024M read-only =
relay-log = mysql-relay-bin
expire_logs_days =
server-id =

# 同样注意 server-id 全局唯一

shell > chown -R mysql.mysql /data/mysql_data

shell > /etc/init.d/mysql.server start

shell > cat /data/mysql_data/xtrabackup_info

binlog_pos = filename 'mysql-bin.000001', position ''

shell > mysql -uxx -pxx -A

mysql> stop slave;
mysql> reset slave all;
mysql> change master to master_host='x.x.x.x',master_user='rep',master_password='xx',master_log_file='mysql-bin.000001',master_log_pos=;
mysql> start slave;
mysql> show slave status\G Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> quit

# master_host='D'
# master_user='rep' # 这里的用户密码,是节点 D 上的,并不是节点 A 上的
# MySQL Slave 节点 E 搭建完成

# 不采用 innobackupex 时,可以使用如下方法:
# stop slave;
# flush tables with read lock;
# show slave status\G
# 记录 master_log_file,master_log_pos
# 新开一个终端,scp -r /data/mysql_data x.x.x.x:/data/mysql_data,拷贝完成就可以 unlock tables;
# chown -R mysql.mysql /data/mysql_data
# /etc/init.d/mysql.server start
# mysql -uxx -pxx -A
# chanage master to ...
# start slave;

# mysql.sock 如果放在 datadir 下,记得 chmod 755 /data/mysql_data,不然 zabbix_agentd 连接不到数据库
# End

上一篇:android Gui系统之SurfaceFlinger(1)---SurfaceFlinger概论


下一篇:ASP.NET XML与JSON