星际宝贝2动画片全集,地盾宝,中国移动福建营业厅
percona xtrabackup工具提供了一种在系统运行时执行mysql数据热备份的方法。
percona xtrabackup在事务系统上执行联机非阻塞，紧密压缩，高度安全的完整备份，因此在计划的维护时段内，应用程序仍保持完全可用。
环境准备：
master:192.168.0.106:3306
slave:192.168.0.105:3306
datadir: /data/mysql/mysql3306/data
cnfpath: /data/mysql/mysql3306/my3306.cnf
xtrabackup 安装，两台机器都安装：
下载地址：https://www.percona.com/downloads/percona-xtrabackup-latest/
我要评论themaster$ wget https://www.percona.com/downloads/percona-xtrabackup-latest/percona-xtrabackup-8.0-7/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm
themaster$ yum install -y percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm
1、在master制作一个备份
themaster$ mkdir xtrabackup_backupfiles
themaster$ xtrabackup --defaults-file=/data/mysql/mysql3306/my3306.cnf -s /tmp/mysql3306.sock -uroot -p'' --backup --target-dir=./xtrabackup_backupfiles
...
completed ok!
为了让快照是一致的:需要prepare备份数据：
themaster$ xtrabackup -s /tmp/mysql3306.sock -uroot -p --prepare --target-dir=./xtrabackup_backupfiles/
...
completed ok!
undo log目录需要处理下，默认会解压到xtrabackup_backupfiles目录下，在my3306.cnf中定义在/data/mysql/mysql3306/data/undolog/下，所以：
themaster$ cd xtrabackup_backupfiles && mkdir undolog && mv undo_* undolog/
2、复制备份数据到slave上
使用rsync或scp将数据从master复制到slave。如果直接将数据同步到slave的数据目录，建议在这之前先停止mysqld。
themaster$ rsync -avpp -e ssh ./xtrabackup_backupfiles/ 192.168.0.105:/data/mysql/mysql3306/data/
复制数据前，您可以备份原始或先前安装的mysql datadir（注意：在移动mysqld的内容或将快照移动到其datadir之前，请确保mysqld已关闭。）：
theslave$ mv /data/mysql/mysql3306/data /data/mysql/mysql3306/data_bak
复制数据后，确保mysql有权限访问
theslave$ chown -r mysql:mysql /data/mysql/mysql3306/data
3、创建master上配置复制账号
themaster|mysql> grant replication slave on *.* to 'repl'@'192.168.0.%' identified by 'repl';
测试连接
theslave$ mysql --host=192.168.0.106 --user=repl --password=repl mysql> show grants;
4、配置slave上的mysql服务器
首先将my3306.cnf从themaster复制到theslave：
theslave$ scp root@192.168.0.106:/data/mysql/mysql3306/my3306.cnf /data/mysql/mysql3306/
theslave$ chown -r mysql:mysql /data/mysql/mysql3306/data/my3306.cnf
修改theslave上的my3306.cnf：
server-id=1053306 # 建议ip最后一位+端口号
启动theslave 上的 mysqld
theslave$ /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
观察error.log是否有错误
theslave$ more /data/mysql/mysql3306/data/error.log
5、启动复制
theslave$ cat /data/mysql/mysql3306/data/xtrabackup_binlog_info mysql-bin.000012 195 e8f74dde-ed8e-11e9-8ebb-000c29f5c092:1-3
执行change master语句在mysql命令行：
theslave|mysql> change master to
master_host='192.168.0.106',
master_user='repl',
master_password='repl',
master_auto_position=1;
开始复制：
theslave|mysql> start slave;
6、检查
theslave|mysql> show slave status \g
...
slave_io_running: yes
slave_sql_running: yes
...
seconds_behind_master: 0
...
看到io和sql线程处于运行中 （yes）表示复制正常。
root@localhost 22:42:41 [(none)]> show slave status\g
error 2006 (hy000): mysql server has gone away
no connection. trying to reconnect...
connection id: 16
current database: *** none ***
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 192.168.0.106
master_user: repl
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000016
read_master_log_pos: 383
relay_log_file: localhost-relay-bin.000002
relay_log_pos: 557
relay_master_log_file: mysql-bin.000016
slave_io_running: yes
slave_sql_running: yes
replicate_do_db:
replicate_ignore_db:
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: 383
relay_log_space: 769
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
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:
replicate_ignore_server_ids:
master_server_id: 1063306
master_uuid: e8f74dde-ed8e-11e9-8ebb-000c29f5c092
master_info_file: mysql.slave_master_info
sql_delay: 0
sql_remaining_delay: null
slave_sql_running_state: slave has read all relay log; waiting for more updates
master_retry_count: 86400
master_bind:
last_io_error_timestamp:
last_sql_error_timestamp:
master_ssl_crl:
master_ssl_crlpath:
retrieved_gtid_set: e8f74dde-ed8e-11e9-8ebb-000c29f5c092:4
executed_gtid_set: e8f74dde-ed8e-11e9-8ebb-000c29f5c092:1-4
auto_position: 1
replicate_rewrite_db:
channel_name:
master_tls_version:
master_public_key_path:
get_master_public_key: 0
network_namespace:
1 row in set (0.00 sec)
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
网友评论