mysql主从复制搭建

实验环境
一,配置文件如下 3306是master 3307是slave
[client]
port=3306
socket=/tmp/mysql_3306.sock

[mysqld_multi]
mysqld = /data/mysql/bin/mysqld_safe
mysqladmin = /data/mysql/bin/mysqladmin
log = /data/mysql/mysqld_multi.log

[mysqld]
user=mysql
basedir = /data/mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3306]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/data
port=3306
server_id=3306
socket=/tmp/mysql_3306.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/log/slow.log
log-error = /data/mysql/log/error.log
binlog_format = mixed
log-bin = /data/mysql/log/mysql3306_bin
secure_file_priv = /tmp/outfile

[mysqld3307]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/3307/data
port=3307
server_id=3307
socket=/tmp/mysql_3307.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/3307/log/slow.log
log-error = /data/3307/log/error.log
binlog_format = mixed
log-bin = /data/3307/log/mysql3307_bin
relay_log=/data/3307/log/relay_log
relay_log_index=/data/3307/log/relay_log_index
relay_log_info_file=/data/3307/log/relay_log.info

二、主库建立复制用户
grant replication slave on *.* to repl@'192.168.220.%' identified by '123';

mysql> show grants for repl@'192.168.220.%';
+----------------------------------------------------------+
| Grants for repl@192.168.220.% |
+----------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.220.%' |
+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

三、
备份主库数据
mysqldump -uroot -p -A --master-data=2 --single-transaction -S /tmp/mysql_3307.sock >/tmp/outfile/full.sql
3.5 将数据恢复到从库
mysql -uroot -p -S /tmp/mysql_3307.sock
set sql_log_bin=0;
source /tmp/outfile/full.sql
set sql_log_bin=1;
3.6 查找备份时间点的binlog位置点
vi /tmp/outfile/full.sql
---CHANGE MASTER TO MASTER_LOG_FILE='mysql3306_bin.000002', MASTER_LOG_POS=154;

mysql> mysql> help change master to
Name: 'CHANGE MASTER TO'
Description:
Syntax:
CHANGE MASTER TO option [, option] ... [ channel_option ]
````````
``````````

CHANGE MASTER TO
MASTER_HOST='192.168.220.150',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql3306_bin.000002',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;

mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.220.150',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql3306_bin.000002',
-> MASTER_LOG_POS=154,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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: 192.168.220.150
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql3306_bin.000002
Read_Master_Log_Pos: 1661
Relay_Log_File: pgdb1-relay-bin.000002
Relay_Log_Pos: 1831
Relay_Master_Log_File: mysql3306_bin.000002
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: 1661
Relay_Log_Space: 2038
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: 3306
Master_UUID: b3d319c3-980e-11ea-9054-000c29293dd3
Master_Info_File: /data/3307/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

 

测试
mysql> use jiacheng;
No connection. Trying to reconnect...
Connection id: 41
Current database: *** NONE ***

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_jiacheng |
+--------------------+
| stg |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from stg;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

mysql> insert into jiacheng values(4);
ERROR 1146 (42S02): Table 'jiacheng.jiacheng' doesn't exist
mysql> insert into stg values(4);
Query OK, 1 row affected (0.01 sec)

mysql>

 

从库查看
mysql> select * from stg;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)

mysql>