【mysql】Linux中Mysql搭建主从复制
主库增加我们的serverId和binlog日志配置,一定要放在mysqld标签下：
[mysqld]
######省略其他的配置信息-------------------------------
#开启日志
log-bin = mysql-bin
# Server Id.
server-id=1
#需要同步的数据库
replicate_wild_do_table=store_db.%
#忽略的数据库
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%
replicate_wild_ignore_table=xxl_job.%
# bin-log 日志保存时长
expire_logs_days = 30
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
从库增加我们的serverId和binlog日志配置：
[mysqld]
######省略其他的配置信息-------------------------------
#开启日志
log-bin = mysql-bin
# Server Id.
server-id=2
#需要同步的数据库
replicate_wild_do_table=store_db.%
#忽略的数据库
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%
replicate_wild_ignore_table=xxl_job.%
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
重启MySQL服务，并且在主库和从库上创建一个测试库名：store_db
1：主库配置
在主库上创建一个新用户，给从库用来读取数据
# 127.0.0.1这里是从库的服务器ip
CREATE USER 'MySlave'@'127.0.0.1' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'MySlave'@'127.0.0.1';
FLUSH PRIVILEGES;
查看Master库状态，记录二进制文件名和位置
# 二进制文件为mysql-bin.000001，位置为528
mysql> show master status;
+------------------+----------+------------------------------------+---------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+------------------------------------+---------------------------------------------+-------------------+
| mysql-bin.000001 | 528 | store_db,product_db_1,product_db_2 | mysql,information_schema,performance_schema | |
+------------------+----------+------------------------------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)
2：从库配置
进入从库，执行命令关联主库
mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3306,MASTER_USER='MySlave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=528;
Query OK, 0 rows affected, 1 warning (0.72 sec)
启动salve同步进程
mysql> start slave;
Query OK, 0 rows affected (0.09 sec)
查看salve的状态
# Slave_IO_Running: Yes，Slave_SQL_Running: Yes时说明两个线程已启动，主从复制配置成功。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: MySlave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 528
Relay_Log_File: DESKTOP-OGAFI1D-relay-bin.000002
Relay_Log_Pos: 320
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: store_db.%,product_db_1.%,product_db_2.%
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 528
Relay_Log_Space: 537
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
Master_UUID: 28c57d6b-59c9-11ed-8fa2-e86a64c02ef3
Master_Info_File: D:\KaiFaGongJu\mysql-5.7.40-winx64-slave\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)
测试
在主数据库新建一个表，刷新从数据库，可以看到这个表。
备注：
在进行数据库主从复制前，主数据库中已有表和数据，则这部分数据不会同步，需要手动导出，并在从数据库中导入。