-
前言：
MYSQL数据库很多情况下都是用于搭建电商平台，电商平台就意味着为公司赚钱的平台，必须24小时在线的；我们也搭建了属于自己的电商平台，但是最近反应需要增加一台从库来缓解主库的读取压力。网上百度了很多相关的方法，总结如下：
-
mysqldump搭建，该方法很简单，但是操作的过程中需要锁表，并停止应用。该方法适合系统未上线时操作，新手可以搭建用于学习；（http://blog.itpub.net/12679300/viewspace-1315062/）
-
Xtrabackup搭建，该方法需要了解Xtrabackup工具的备份还原，可以实现在线搭建主从架构。
mysql一直推荐的企业版的mysql备份工具：mysqlbackup，抱着学习的心态就在测试环境中通过mysqlbakcup工具来搭建主从架构；
在进行以下操作之前，需要先进行以下两个设置：主库和备库的参数文件已经修改完成、在主库上面创建主从连接用户；
以下是详细的整理步骤：
| 数据库 |
主机名 |
IP地址 |
同步用户 |
备份位置 |
| 主数据库 |
Mysql01 |
192.168.47.152 |
server01 |
/backup |
| 从数据库 |
Mysql02 |
192.168.47.151 |
2 主库的操作步骤
2.1 对主库进行全备，脚本如下
| mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log 备份的目录为/backup，请确认这个目录的存在； |
2.2 记录这个时候主库的binlog状态
| mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 107 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) |
2.3 为了验证主从是没有问题的，这个时候可以往主库的某个数据库插入数据，然后再记录状态
| mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 500 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) |
2.4 把备份从主库拷贝到从库
| [root@mysql01 backup]# service iptables stop iptables：清除防火墙规则： [确定] iptables：将链设置为政策 ACCEPT：filter [确定] iptables：正在卸载模块： [确定] root@mysql01 backup]# scp bak.tar root@mysql02:/backup/* Warning: Permanently added the RSA host key for IP address '192.168.47.151' to the list of known hosts. root@mysql02's password: bak.tar 100% 69MB 23.1MB/s 00:03 需要先关闭防火墙 |
3.从库的操作步骤
3.1 进行从库的恢复
| [root@mysql01 backup]# mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014/08/26] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
mysqlbackup: INFO: Starting with following command line ... mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back
mysqlbackup: INFO: IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful 'copy-back' run mysqlbackup prints "mysqlbackup completed OK!".
141118 16:19:35 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2014-11-18.16-19-35_copy_back.log
-------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /data/mysql innodb_data_home_dir = /data/mysql innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /data/mysql/ innodb_log_files_in_group = 2 innodb_log_file_size = 5242880 innodb_page_size = Null innodb_checksum_algorithm = none
-------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /backup/datadir innodb_data_home_dir = /backup/datadir innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /backup/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 5242880 innodb_page_size = 16384 innodb_checksum_algorithm = none
mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 141118 16:19:35 mysqlbackup: INFO: Copy-back operation starts with following threads 1 read-threads 1 write-threads mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with --log-bin. Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. 141118 16:19:35 mysqlbackup: INFO: Copying /backup/datadir/ibdata1. 141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'john' 141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'mysql' 141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'performance_schema' 141118 16:19:37 mysqlbackup: INFO: Completing the copy of all non-innodb files. 141118 16:19:37 mysqlbackup: INFO: Copying the log file 'ib_logfile0' 141118 16:19:37 mysqlbackup: INFO: Copying the log file 'ib_logfile1' 141118 16:19:39 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql 141118 16:19:39 mysqlbackup: INFO: Copy-back operation completed successfully. 141118 16:19:39 mysqlbackup: INFO: Finished copying backup files to '/data/mysql' |
3.2 进行授权，并打开数据库
| [root@mysql02 data]# chmod -R 777 mysql [root@mysql02 data]# service mysqld start Starting MySQL [确定] |
3.3 从库连接到主库
| mysql> CHANGE MASTER TO MASTER_HOST='mysql01',MASTER_USER='server01',MASTER_PASSWORD='server01', MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=107; |
3.4 启动从库服务
| mysql> start slave; Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql01 Master_User: server01 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 1082 Relay_Log_File: mysql02-relay-bin.000003 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: john 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: 1082 Relay_Log_Space: 1532 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 1 row in set (0.00 sec) |
3.5 经过以上检查主从同步已经没有问题了，需要再验证相应的表数据；
| mysql> select * from wzq; +-------+ | name | +-------+ | qiang | | wu | | zhi | | 1 | | 2 | | 3 | +-------+ 8 rows in set (0.00 sec) |
-
总结：经过验证通过msyqlbackup搭建主从的数据库，可以实现不停机进行搭建，整个过程也是挺简单的，但是是在测试环境中进行的，真正的生产环境还要再考虑详细的步骤。
*********************************************************************************************************************
本文作者：JOHN QQ：1916066696 （请备注数据库）
ORACLE技术博客：ORACLE 猎人笔记 http://blog.itpub.net/12679300/
请扫描加微信号！
********************************************************************************************************************
