主从搭建方案（一主一从）
MySQL双机主从集群架构，可以实现MySQL数据库数据的安全性和高可用性。在每台MySQL数据库服务器上安装一套MySQL程序，数据库物理文件均在本机硬盘中，通过MySQL主从复制架构，即时同步数据，如果主服务器MySQL数据库宕机，另外一台备机可以通过手动切换暂时承担起全部功能，并且可以根据存活主机的数据库及时完整恢复宕机服务器的数据库，以达到高可用性的目的。
搭建环境
master：4核 2G内存 CentOS 6.5 IP：192.168.18.136 主机名：centos6h3 安装MySQL 5.6.36
slave：
4核 2G内存 CentOS 6.5 IP：192.168.18.137 主机名：centos6h4
安装
MySQL 5.6.36
搭建过程
1、在主库建立一个账号，使得从库可以连接[192.168.18.137 是从库地址]
1
mysql> grant replication slave on *.* to 'backup'@'192.168.18.137' identified by '123456';
2
Query OK, 0 rows affected, 1 warning (0.00 sec)
判断标准:
从库测试连接
1
[root@centos6h4 ~]# mysql -ubackup -p123456 -h192.168.18.136
2
mysql: [Warning] Using a password on the command line interface can be insecure.
1
[mysqld]
2
log_bin=mysql-server
3
server-id=1
4
重启数据库，show variables like '%%' 查看修改生效
3、 在主库做一个全备，最好使用xtrabackup,因为我们需要知道binlog 恢复的起点
1
innobackupex --user=root --password=123456 --no-timestamp /backup
4、 在从库安装一个和主库完全一致的mysql 软件，不需要初始化数据库，如果已经初始化，手工删除
5、修改从库的配置文件
1
server_id=2
6、使用scp -r 将主库的备份传递给从库
1
scp -r /backup/ root@192.168.18.137:/
2
chown -R mysql: /backup
7、在从库上执行xbackup 进行恢复，恢复完成以后，chown 修改数据目录权限。(恢复完成确定binlog 日志到哪个位置)
1
innobackupex --defaults-file=/etc/my.cnf --move-back /backup/
8、chown修改datadir权限，启动从库
1
chown -R mysql: /data/
2
mysqld_safe --defaults-file=/etc/my.cnf &
9、 搭建主从关系【确定binlog 位置，主库的位置】
查看主全备信息
1
[root@centos6h3 backup]# cat /backup/xtrabackup_info
2
binlog_pos = filename 'mysql-server.000006', position '451'
查看恢复备份的从库日志信息，发现是一致的
1
[root@centos6h4 data]# cat /data/xtrabackup_info
2
binlog_pos = filename 'mysql-server.000006', position '451'
在从库建立主从关系
1
mysql> CHANGE MASTER TO MASTER_HOST='192.168.18.136',MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='mha-server.000006',MASTER_LOG_POS=451,MASTER_CONNECT_RETRY=10;
在从库启动主从
1
mysql> start slave;
10、从库执行show processlist ; #查看io 和sql 线程都启动
1
mysql> show processlist;
2
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3
| Id | User | Host | db | Command | Time | State | Info |
4
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
5
| 1 | root | localhost | NULL | Query | 0 | init | show processlist |
6
| 6 | system user | | NULL | Connect | 30 | Waiting for master to send event | NULL |
7
| 7 | system user | | NULL | Connect | 59 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
8
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
9
3 rows in set (0.00 sec)
1
mysql> show processlist;
2
+----+--------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
3
| Id | User | Host | db | Command | Time | State | Info |
4
+----+--------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
5
| 2 | root | localhost | NULL | Query | 0 | init | show processlist |
6
| 29 | backup | 192.168.18.137:37616 | NULL | Binlog Dump | 37 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
7
+----+--------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
8
2 rows in set (0.00 sec)
12、 从库 show slave status \G 查看io 和sql 有无错误
1
mysql> show slave status \G
2
*************************** 1. row ***************************
3
Slave_IO_State: Waiting for master to send event
4
Master_Host: 192.168.18.136
5
Master_User: backup
6
Master_Port: 3306
7
Connect_Retry: 10
8
Master_Log_File: mysql-server.000006
9
Read_Master_Log_Pos: 333
10
Relay_Log_File: mysqld-relay-bin.000004
11
Relay_Log_Pos: 499
12
Relay_Master_Log_File: mysql-server.000006
13
Slave_IO_Running: Yes
14
Slave_SQL_Running: Yes
15
Replicate_Do_DB:
16
Replicate_Ignore_DB:
17
Replicate_Do_Table:
18
Replicate_Ignore_Table:
19
Replicate_Wild_Do_Table:
20
Replicate_Wild_Ignore_Table:
21
Last_Errno: 0
22
Last_Error:
23
Skip_Counter: 0
24
Exec_Master_Log_Pos: 333
25
Relay_Log_Space: 839
26
Until_Condition: None
27
Until_Log_File:
28
Until_Log_Pos: 0
29
Master_SSL_Allowed: No
30
Master_SSL_CA_File:
31
Master_SSL_CA_Path:
32
Master_SSL_Cert:
33
Master_SSL_Cipher:
34
Master_SSL_Key:
35
Seconds_Behind_Master: 0
36
Master_SSL_Verify_Server_Cert: No
37
Last_IO_Errno: 0
38
Last_IO_Error:
39
Last_SQL_Errno: 0
40
Last_SQL_Error:
41
Replicate_Ignore_Server_Ids:
42
Master_Server_Id: 1
43
Master_UUID: 84dd2779-566e-11e7-b734-000c29935598
44
Master_Info_File: /data/master.info
45
SQL_Delay: 0
46
SQL_Remaining_Delay: NULL
47
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
48
Master_Retry_Count: 86400
49
Master_Bind:
50
Last_IO_Error_Timestamp:
51
Last_SQL_Error_Timestamp:
52
Master_SSL_Crl:
53
Master_SSL_Crlpath:
54
Retrieved_Gtid_Set:
55
Executed_Gtid_Set:
56
Auto_Position: 0
57
1 row in set (0.00 sec)
13、 主库上执行dml 和ddl ，确定从库是否能有数据同步过来
193
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
