mysql> create table test_tb(id int,name varchar(30));
6. 从库导入备份库
# mysql -uroot -p123 -e 'create database weibo;'
# mysql -uroot -p123 weibo < weibo.sql
7. 在备份文件weibo.sql查看binlog和pos值
# head -25 weibo.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; #大概22行
8. 从库设置从这个日志点同步，并启动
| mysql> change master to master_host='192.168.18.212', -> master_user='sync', -> master_password='sync', -> master_log_file='mysql-bin.000001', -> master_log_pos=107; mysql> start slave; mysql> show slave statusG; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 90 Current database: *** NONE *** *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.18.212 Master_User: sync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 358 Relay_Log_File: mysqld-relay-bin.000003 Relay_Log_Pos: 504 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...... |
可以看到IO和SQL线程均为YES，说明主从配置成功。
9. 从库查看weibo库里面的表
| mysql> show tables; +---------------------------+ | Tables_in_weibo | +---------------------------+ | test_tb | |
发现刚才模拟创建的test_tb表已经同步过来!
二、xtrabackup方式(推荐)
在上面配置基础上做实验，先删除掉从库配置：
mysql> stop slave; #停止同步
mysql> reset slave; #清除从连接信息
mysql> show slave statusG; #再查看从状态，可以看到IO和SQL线程都为NO
mysql> drop database weibo; #删除weibo库
此时，从库现在和新装的一样，继续前进!
1. 主库使用xtrabackup备份
# innobackupex --user=root --password=123 ./
生成一个以时间为命名的备份目录：2015-07-01_16-49-43
# ll 2015-07-01_16-49-43/
total 18480
drwxr-xr-x 5 root root 4096 Jul 1 16:49 ./
drwx------ 4 root root 4096 Jul 1 16:49 ../
-rw-r--r-- 1 root root 188 Jul 1 16:49 backup-my.cnf
-rw-r----- 1 root root 18874368 Jul 1 16:49 ibdata1
drwxr-xr-x 2 root root 4096 Jul 1 16:49 mysql/
drwxr-xr-x 2 root root 4096 Jul 1 16:49 performance_schema/
drwxr-xr-x 2 root root 12288 Jul 1 16:49 weibo/
