Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.