1.1 数据库主从搭建
主：b6 port 3306 IP：192.168.241.6
从：b11 port 3306 IP：192.168.241.11
1.1.1 主库设置server-id 并开启binlog功能
B6
mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 6 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
配置文件配置
[mysqld]
port = 3306
socket = /disk1/mysql/mysql.sock
default-time_zone = '+8:00'
datadir = /disk1/mysql_data/
skip-external-locking
skip-name-resolve
check slow
slow-query-log = ON
long_query_time = 2
log-queries-not-using-indexes = ON
default-storage-engine = Innodb
pid-file = /disk1/mysql_data//b6.pid
slow-query-log-file = /disk1/mysql_data//b6-slow.log
server-id = 6
log-bin = /disk1/mysql_data//mysql-bin
1.1.2 从库设置server-id
B11
mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 11 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
配置文件配置
[mysqld]
port = 3306
socket = /disk1/mysql/mysql.sock
default-time_zone = '+8:00'
datadir=/disk1/mysql_data/
skip-external-locking
skip-name-resolve
check slow
slow-query-log = ON
long_query_time = 2
log-queries-not-using-indexes = ON
default-storage-engine = Innodb
pid-file = /disk1/mysql_data//b11.pid
slow-query-log-file = /disk1/mysql_data//b11-slow.log
server-id = 11
1.1.3 主库建立用于主从复制的账号
mysql> grant replication slave on . to 'rep'@'192.168.241.%' identified by 'maixun123';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
1）. ：表示所有库所有表，也可以制定具体的库和表进行复制
2）'rep'@'192.168.241.%'：rep 为同步账号，192.168.241.%为授权主机网段。
3）flush privileges; 刷新权限，使授权生效
1.1.3.1 检查主库创建的rep复制账号
mysql> select user,host from mysql.user;
+-------+---------------+
| user | host |
+-------+---------------+
| oopin | 192.168.241.% |
| rep | 192.168.241.% |
| root | 192.168.241.% |
| | b6 |
| | localhost |
| oopin | localhost |
| root | localhost |
+-------+---------------+
7 rows in set (0.00 sec)
mysql>
mysql> select user,host from mysql.user where user='rep';
+------+---------------+
| user | host |
+------+---------------+
| rep | 192.168.241.% |
+------+---------------+
1 row in set (0.00 sec)
rep账号授权的权限
mysql> show grants for rep@'192.168.241.%';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.241.% |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON . TO 'rep'@'192.168.241.%' IDENTIFIED BY PASSWORD '*E14DCA0F10B331A3F100CF23374EC3B84B537254' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
1.1.4 锁表导出数据
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
1.1.5 查看位置点
注意：show master status 命令显示的信息要记录，后面的从库导入全备后，继续和主库复制时就要从这个位置开始
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 408 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1.1.6 新开窗口备份全局主库数据
mysqldump -uroot -p'XXX' -S /disk/mysql/mysql.sock -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
执行这条主库备份不用锁表
mysqldump -uroot -p'XXX'--master-data=2 --single-transaction -S /disk/mysql/mysql.sock -A -B >/server/backup/mysql_bak.$(date +%F).sql
-A：备份所有的数据库
-B:备份的数据库，后面可以指定多个库名
--master-data
在备份结果中增加binlog日志文件名对应的binlog位置点（即CHANGE MASTER...语句）。值为1时不注释状态，值为2时是注释状态，该参数执行会打开--lock-all-tables功能，除非有--single-transaction在，使用该参数时会关闭--lock-tables 功能。
1.1.7 窗口解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
1.1.8 主库全局数据导入到从库
cd /server/backup/
zcat mysql_bak.2017-05-04.sql.gz >mysql_bak.2017-05-04.sql
mysql -uroot -p'XXX'-S /data/data/mysql.sock <mysql_bak.2017-05-04.sql
1.2 从库连接主库记录binlog位置点开始向下同步（从库）
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.241.6',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='maixun123',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=408;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
解释说明=====================================
CHANGE MASTER TO
MASTER_HOST='192.168.241.6', #主库
MASTER_PORT=3306, #主库端口，从库端口可以和主库不同
MASTER_USER='rep', #主库建立用于复制的用户rep
MASTER_PASSWORD='maixun123', #rep用户密码
MASTER_LOG_FILE='mysql-bin.000001', #show master status 时看到的二进制文件名称
MASTER_LOG_POS=408; #show master status 时看到的二进制日志偏移量
=========================================================================
上面操作实际就是把用户等信息写入到从库新的master info 文件中。
[root@b11 mysql_data]# ll
total 602288
-rw-rw----. 1 mysql mysql 56 Jun 13 17:33 auto.cnf
-rw-r-----. 1 mysql mysql 109467 Sep 8 11:50 b11.err
-rw-rw---- 1 mysql mysql 6 Sep 8 11:06 b11.pid
-rw-rw---- 1 mysql mysql 171 Sep 8 11:50 b11-relay-bin.000001
-rw-rw---- 1 mysql mysql 472 Sep 8 11:57 b11-relay-bin.000002
-rw-rw---- 1 mysql mysql 46 Sep 8 11:50 b11-relay-bin.index
-rw-rw---- 1 mysql mysql 1253 Sep 8 11:06 b11-slow.log
-rw-rw---- 1 mysql mysql 128 Sep 8 11:57 master.info
drwx------. 2 mysql mysql 4096 Jun 13 17:33 mysql
-rw-rw---- 1 mysql mysql 143 Sep 8 11:06 mysql-bin.000001
-rw-rw---- 1 mysql mysql 35 Sep 8 10:57 mysql-bin.index
-rw-rw---- 1 mysql mysql 56 Sep 8 11:57 relay-log.info
[root@b11 mysql_data]# cat master.info
23
mysql-bin.000001
597
192.168.241.6
rep
。。。。。。省略
1.3 启动从库同步开关，并查看状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.241.6
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 408
Relay_Log_File: b11-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
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: 408
Relay_Log_Space: 454
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: 6
Master_UUID: 51149856-501b-11e7-a28d-6f97a7808c3e
Master_Info_File: /disk1/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:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
状态参数：
Slave_IO_Running: Yes #这个是I/O线程状态，I/O线程负责从从库到主库读取binlog日志，并写入到从库中的中继日志，状态为yes表示I/O线程工作正常
Slave_SQL_Running: Yes #这个是SQL线程状态，SQL线程负责读取中继日志（relay-log）中的数据并转换为SQL语句应用到从数据库中，状态为Yes表示I/O线程工作正常。
Seconds_Behind_Master: 0 #这个是复制过程中从库比主库延迟的秒数