How to configure Master-slave in Mysql
Editor to share with you how to configure Master-slave in Mysql, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!
Set up the environment of mysql master slave
Here is a brief record.
Mysql database version: 5.7-18
Both master and slave initialize the mysql database in the following way
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
My.cnf configuration for Master:
[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
Configuration file for 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
Note: the parameter master-host 5.7 is no longer supported.
Reference:
Https://blog.csdn.net/edwzhang/article/details/8819629
After the initial session is complete, export the mysql database through mysqldump in master
Session 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 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)
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)
This Master Status is important and needs to be relied on in subsequent slave configurations
Mysqldump-u root-p-- all-databases-- master-data > / root/dbdump.db
After export, release the lock
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)
Import on the slave side, restart the mysql service after import
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)
Test on the masterside:
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)
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 >
If the configuration is correct, you should be able to see the data on the server
These are all the contents of the article "how to configure Master-slave in Mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
