mysql数据库 生成 lib_对运行中的Mysql数据库创建从库

所需软件：

libev-4.15-6.el7.x86_64.rpm

percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm

首先参考http://blog.itpub.net/28536251/viewspace-2138854/分别在两节点安装Mysql。

1、主库从库安装备份软件

[root@D2-wetestdb160 ~]# yum install -y perl-DBD-MySQL perl-DBI  perl-Time-HiRes

[root@D2-wetestdb160 ~]# rpm -ivh libev-4.15-6.el7.x86_64.rpm percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm

2、主库备份数据库

[root@D2-wetestdb160 ~]# mkdir /data/backup

[root@D2-wetestdb160 ~]# innobackupex --user=root --password=123456 /data/backup

[root@D2-wetestdb160 ~]# tar -cvzf mysqlbackup84106_0113.tar.gz /data/backup/2017-01-13_11-17-22/

[root@D2-wetestdb160 ~]# scp mysqlbackup84106_0113.tar.gz root@10.163.89.245:/root

3、从库进行恢复

[root@D2-stone245 ~]# tar -xvzf mysqlbackup84106_0113.tar.gz -C /var/lib/mysql

[root@D2-stone245 ~]# innobackupex --apply-log /var/lib/mysql/data/backup/2017-01-13_11-17-22/

[root@D2-stone245 ~]# chown -R mysql:mysql /var/lib/mysql/data/backup/2017-01-13_11-17-22

4、从库修改配置文件

[root@D2-stone245 ~]# /etc/init.d/mysqld stop

[root@D2-stone245 ~]# vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql/data/backup/2017-01-13_11-17-22/

server-id=89254

[root@D2-stone245 ~]# /etc/init.d/mysql.server start

5、从库配置复制

[root@D2-stone245 ~]# cat /var/lib/mysql/data/backup/2017-01-13_11-17-22/xtrabackup_binlog_info

mysql-bin.000010        100927559

mysql> CHANGE MASTER TO MASTER_HOST='10.163.84.160',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='replsafe',MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=100927559;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> set global read_only=1;

Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like "%read_only%";

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| innodb_read_only | OFF   |

| read_only        | ON    |

| super_read_only  | OFF   |

| tx_read_only     | OFF   |

+------------------+-------+

4 rows in set (0.01 sec)

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.163.84.160

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 1719

Relay_Log_File: D2-stone245-relay-bin.000003

Relay_Log_Pos: 1932

Relay_Master_Log_File: mysql-bin.000011

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

Relay_Log_Space: 6430

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

Master_UUID: 9ee791f2-ad84-11e6-932d-0050568a6255

Master_Info_File: /var/lib/mysql/data/backup/2017-01-13_11-17-22/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:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)