mysql -- mysql主从部署

一、MySQL主从复制
安装工具xtrabackup(mysql主从都安装):
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/7/x86_64/percona-xtrabackup-2.3.4-1.el7.x86_64.rpm
yum -y localinstall percona-xtrabackup-2.3.4-1.el7.x86_64.rpm

 

创建主从用户（主库设置）：
mysql>grant replication slave,reload,super on *.* to slave@192.168.1.230 identified by '123456';

 

主库创建测试内容:
mysql> create database tongbu;
mysql>use tongbu
mysql>create table test (
id int not null auto_increment,
name varchar(10) not null,
primary key (id)
);

 

mysql> insert into test values(1,'li');
mysql> insert into test values(2,'wang');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(3,'liu');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tongbu.test;
+----+------+
| id | name |
+----+------+
| 1 | li |
| 2 | wang |
| 3 | liu |
+----+------+
3 rows in set (0.00 sec)

 

1.备份（MySQL master端）
[root@linux-node1 ~]#innobackupex --defaults-file="/usr/local/mysql/my.cnf" --user=root --socket=/usr/local/mysql/mysql.sock /opt

[root@linux-node1 ~]# ll /opt/
total 8
drwx------ 5 root root 4096 Dec 25 18:06 2015-12-25_18-06-51   --->产生的目录

 

2.scp 到从库
[root@linux-node1 ~]# scp -r 2015-12-25_18-06-51/ 192.168.1.230:/opt/

 

3.预处理（MySQL slave端）
[root@linux-node2 mysql]# innobackupex --defaults-file="/usr/local/mysql/my.cnf" --user=root --socket=/usr/local/mysql/mysql.sock --apply-log --user-memory=1G /opt/2015-12-25_18-06-51
注意：--user-memory的大小依据机器内存的大小，尽量给的大些，这样执行的速度比较快。
2015-12-25_17-31-18/目录是上面备份操作后产生的目录。

4.关闭从库并且删除/usr/local/mysql/data/目录下的所有内容。

 

5.copy数据
[root@linux-node2 data]# innobackupex --defaults-file="/usr/local/mysql/my.cnf" --user=root --socket=/usr/local/mysql/mysql.sock --move-back /opt/2015-12-25_18-06-51

查看/usr/local/mysql/data/目录下生成的新的内容：
[root@linux-node2 data]# ll
total 536596
-rw-r----- 1 root root 918 Dec 25 18:08 ib_buffer_pool
-rw-r----- 1 root root 12582912 Dec 25 18:09 ibdata1
-rw-r--r-- 1 root root 268435456 Dec 25 18:09 ib_logfile0
-rw-r--r-- 1 root root 268435456 Dec 25 18:09 ib_logfile1
drwx------ 2 root root 4096 Dec 25 18:10 mysql
drwx------ 2 root root 4096 Dec 25 18:10 performance_schema
drwx------ 2 root root 49 Dec 25 18:10 tongbu
-rw-r--r-- 1 root root 22 Dec 25 18:09 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 591 Dec 25 18:08 xtrabackup_info

 

6.在从库上查看master位置
从库上查看位置点：
[root@linux-node2 data]# cat xtrabackup_binlog_pos_innodb
mysql-bin.000004 1410

 

7.修改权限
[root@linux-node2 ~]#chown -R mysql.mysql /usr/local/mysql/

 

8.启动从库服务
[root@linux-node2 ~]#/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf &

 

9.设置主从
mysql>change master to master_host='192.168.1.240',master_user='slave',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=1410;
mysql>start slave;
mysql>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.240
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1684
Relay_Log_File: linux-node2-relay-bin.000002
Relay_Log_Pos: 588
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mydb.sp_counter
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1684
Relay_Log_Space: 798
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: ffe03b1b-ca88-11e6-bb8d-000c29bc301b
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: ffe03b1b-ca88-11e6-bb8d-000c29bc301b:7
Executed_Gtid_Set: ffe03b1b-ca88-11e6-bb8d-000c29bc301b:7
Auto_Position: 0
1 row in set (0.00 sec)

 

测试主从同步
主库创建数据：
mysql> insert into test values(4,'tan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tongbu.test;
+----+------+
| id | name |
+----+------+
| 1 | li |
| 2 | wang |
| 3 | liu |
| 4 | tan |
+----+------+

 

从库查看：
mysql> select * from tongbu.test;
+----+------+
| id | name |
+----+------+
| 1 | li |
| 2 | wang |
| 3 | liu |
| 4 | tan |      --->已经同步过来。
+----+------+