mysql 主从同步及备份

最新推荐文章于 2022-08-20 01:28:13 发布

weixin_33713350

最新推荐文章于 2022-08-20 01:28:13 发布

阅读量103

点赞数

文章标签：数据库运维

原文链接：http://blog.51cto.com/132408/1951328

版权

Mysql 主从复制搭建

mastermysql server my.cnf 配置内容如下

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

server-id = 1

port = 3306

basedir = /usr/bin

bind-address = 0.0.0.0

datadir = /var/lib/mysql

socket = /tmp/mysql.sock

log_error = /var/lib/mysql/mysql-error.log

log-bin=mysql-bin

binlog_format = ROW

innodb_buffer_pool_size = 1G

innodb_file_per_table=1

character-set-server = utf8

启动mysql,登陆数据库

mysql>CREATE DATABASE IF NOT EXISTS cattleCOLLATE = 'utf8_general_ci' CHARACTER SET = 'utf8';

mysql>GRANT ALL ON cattle.* TO'cattle'@'%' IDENTIFIED BY 'cattle';

mysql>GRANT ALL ON cattle.* TO'cattle'@'localhost' IDENTIFIED BY 'cattle';

slave mysql server my.cnf 配置文件：

[client]

port = 3306

[mysqld]

server-id = 2

port = 3306

basedir = /usr/bin

bind-address = 0.0.0.0

datadir = /var/lib/mysql

socket = /tmp/mysql.sock

log_error = /var/lib/mysql/mysql-error.log

log-bin=mysql-bin

binlog_format = ROW

innodb_buffer_pool_size = 1G

innodb_file_per_table=1

character-set-server = utf8

relay-log = slave-relay-bin

log-slave-updates

sync_relay_log = 1

开启ranch server 连接外部数据库：

docker run -d --restart=unless-stopped -p8080:8080 rancher/server \

--db-host 10.0.0.53--db-port 3306 --db-user cattle --db-pass cattle --db-name cattle

Tip --db-host 10.0.0.53 master mysql serverip address

登陆rancher server： https:// 10.0.0.53:8080

Master mysql server 复制账号设置：

Mysql>grant replication slave on *.* to slave@'%' identified by '123456';

备份主库数据库：

记录备份时mysql 主库备份时的log日志文件名和pos位置：

mysql>mysql> show master status\G

*************************** 1. row***************************

File: mysql-bin.000024

Position: 7187

Binlog_Do_DB: cattle

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.01 sec)

备份mysql主库的cattle 数据库：

mysqldump-uroot -p --opt --flush-logs --databases cattle >mysql-cattle-bak.sql

在slave 从库服务器还原：

Mysql–uroot –p < mysql-fullbak-vgtest3.sql

在slave 从库执行如下命令：

mysql>CHANGE MASTER TO MASTER_HOST = '10.0.0.53', MASTER_USER = 'slave',MASTER_PORT = 3306, MASTER_PASSWORD = '123456', master_log_file='mysql-bin.000012',master_log_pos= 560332;

mysql>start slave;

mysql> show slave status\G

*************************** 1. row***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 10.0.0.53

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000025

Read_Master_Log_Pos: 14761

Relay_Log_File:slave-relay-bin.000004

Relay_Log_Pos: 14974

Relay_Master_Log_File: mysql-bin.000025

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: cattle

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: 14761

Relay_Log_Space: 15228

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: 1

Master_UUID:6cd9cf95-7018-11e7-acf6-080027e889c6

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

Tip: 如果Slave_IO_Running:Yes ; Slave_SQL_Running: Yes 证明复制设置成功。

Mysql 数据库备份软件xtrabackup安装

下载并安装软件安装源

wgethttps://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb

dpkg-i percona-release_0.1-4.$(lsb_release -sc)_all.deb

apt-getupdate

apt-getinstall percona-xtrabackup-24

mysql数据库的全备份：

innobackupex --defaults-file=/etc/mysql/my.cnf--user=root --password=123456 --host=10.0.0.52 --socket=/tmp/mysql.sock --no-timestamp /opt/mysqlbak

全备份恢复

停止数据库：

Servicemysql stop

删除数据库数据目录文件（比如在/var/lib/mysql）：

Rm -rf/var/lib/mysql/*

使用xtrabackup恢复：

innobackupex --apply-log /opt/mysqlbak

innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /opt/mysqlbak

启动mysql：

chown -R mysql.mysql /var/lib/mysql

Service mysql start

增量备份

第一次增量备份：

innobackupex--defaults-file=/etc/mysql/my.cnf --user=root --password=123456--host=10.0.0.52 --socket=/tmp/mysql.sock --no-timestamp --incremental-basedir=/opt/mysqlbaktest/mysqlfullbak--incremental /opt/mysqlbaktest/increment1

tip: incremental-basedir：指向全备份的目录

第2次增量备份：

innobackupex--defaults-file=/etc/mysql/my.cnf --user=root --password=123456--host=10.0.0.52 --socket=/tmp/mysql.sock --no-timestamp --incremental-basedir=/opt/mysqlbaktest/increment1--incremental /opt/mysqlbaktest/increment2

tip: incremental-basedir:指向第一次增量备份的目录。