Mysql中Master-slave如何配置

小编给大家分享一下Mysql中Master-slave如何配置，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
搭建了一下mysql master slave的环境
在此做一下简单记录
mysql数据库版本:5.7-18

master与slave均采用了如下方式初始化mysql数据库
mkdir -p /data/mysql
useradd mysql
chown -R mysql:mysql /data/
chown -R mysql:mysql /usr/local/mysql*
/usr/local/mysql/bin/mysqld –defaults-file=/etc/my.cnf –datadir=/data/mysql/ –user=mysql –initialize-insecure
Master的my.cnf配置：
[client]
port = 3306
socket = /tmp/mysql.sock
#default-character-set=utf8
[mysql]
#default-character-set=utf8
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
server_id=151
open_files_limit = 3072
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 512
external-locking = FALSE
max_allowed_packet = 128M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 51
query_cache_size = 32M
tmp_table_size = 96M
max_heap_table_size = 96M
slow_query_log = 1
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 0.05
log-bin = /data/mysql/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 128M
max_binlog_size = 1024M
expire_logs_days = 7
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
character-set-server=utf8
default-storage-engine=InnoDB
binlog_format=row
#gtid_mode=on
#log_slave_updates=1
#enforce_gtid_consistency=1
interactive_timeout=100
wait_timeout=100
transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1434M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 0
[mysqldump]
quick
max_allowed_packet = 32M
Slave的配置文件：/etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
#default-character-set=utf8
[mysql]
#default-character-set=utf8
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
server_id=152
#master slave replicat
#master-host=192.168.43.151
#master-user=repl
#master-password=repl
relay-log=/data/mysql/mysql-replay-bin
master-info-file = /data/mysql/mysql-master.info
relay-log-info-file = /data/mysql/mysql-relay-log.info
open_files_limit = 3072
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 512
external-locking = FALSE
max_allowed_packet = 128M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 51
query_cache_size = 32M
tmp_table_size = 96M
max_heap_table_size = 96M
slow_query_log = 1
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 0.05
log-bin = /data/mysql/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 128M
max_binlog_size = 1024M
expire_logs_days = 7
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
character-set-server=utf8
default-storage-engine=InnoDB
binlog_format=row
#gtid_mode=on
#log_slave_updates=1
#enforce_gtid_consistency=1
interactive_timeout=100
wait_timeout=100
transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1434M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 0
[mysqldump]
quick
max_allowed_packet = 32M 注意：master-host这个参数5.7已经不支持。
参考：
https://blog.csdn.net/edwzhang/article/details/8819629

初始话完成之后，在master通过mysqldump导出mysql数据库
会话1：
[root@mysql01 ~]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle i免费主机域名s a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’ IDENTIFIED BY ‘repl’;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000017 | 581 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

这个Master Status很重要，在其后的slave配置中需要依赖它
mysqldump -u root -p –all-databases –master-data > /root/dbdump.db

导出之后，释放锁
mysql> unlock tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 5
Current database: *** NONE ***
Query OK, 0 rows affected (0.00 sec)

slave端导入， 导入之后重启mysql服务
mysql -u root -p
在slave端启用复制：
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.43.151′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’repl’,
-> MASTER_LOG_FILE=’mysql-bin.000017′,
-> MASTER_LOG_POS=581;
Query OK, 0 rows affected, 2 warnings (0.30 sec)
mysql>
mysql> start slave;
Query免费主机域名 OK, 0 rows affected (0.00 sec)

在master端进行测试:
mysql> create database test2;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 7
Current database: *** NONE ***
Query OK, 1 row affected (0.28 sec)
mysql>
mysql> show slave staus
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘staus’ at line 1
mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000017 | 743 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)
mysql>
mysql> use test02
No connection. Trying to reconnect…
Connection id: 8
Current database: *** NONE ***
ERROR 1049 (42000): Unknown database ‘test02’
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
+——————–+
5 rows in set (0.00 sec)
mysql> use test2
Database changed
mysql>
mysql>
mysql> create table mytest01(pid int, nme varchar(100));
Query OK, 0 rows affected (0.34 sec)
mysql>
mysql> insert into mytest01 values(1, ‘AAAA’);
Query OK, 1 row affected (0.04 sec)
mysql>

如果配置正确，应当可以在slave端看到数据

以上是“Mysql中Master-slave如何配置”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注云技术行业资讯频道！

相关推荐: Oracle RMAN恢复测试的方法教程

本篇内容主要讲解“Oracle RMAN恢复测试的方法教程”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Oracle RMAN恢复测试的方法教程”吧!1 启动归档2 创建测试数据3 创建0级备份脚本执行0级备份执行…