Mysql中Master

44次阅读

没有评论

共计 6344 个字符，预计需要花费 16 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 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)

Oracle is 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)

这个 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 /root/dbdump.db

在 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 ***