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

This article focuses on "how to achieve mysql 5.7master synchronization", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "how to achieve mysql 5.7 master synchronization"!

one。 Environment:

OS:CentOS 6.5 X64

DB Version:Percona Mysql 5.7.15-9-log

Path: / app/mysql57

Data file path: / data/mysql57/data

DB1:192.168.213.4

DB2:192.168.213.5

two。 Install Mysql

1. Install dependency packages

Yum-y install gcc gcc-c++ ncurses ncurses-devel cmake readline-devel

two。 Create the required directory

Mkdir-pv / app/mysql57

Mkdir-pv / data/mysql56/data/

3. Create users and empower them

Useradd-M-s / sbin/nologin mysql

Chown-R mysql:mysql / app/mysql57

Chown-R mysql:mysql / data/mysql57

4. Download the required directory

Cd / usr/local/tools

Wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

Tar xzf boost_1_59_0.tar.gz

Wget https://www.percona.com/downloads/Percona-Server-5.7/Percona-Server-5.7.15-9/source/tarball/percona-server-5.7.15-9.tar.gz

Tar xf percona-server-5.7.15-9.tar.gz

Cd percona-server-5.7.15-9

Cmake. -DCMAKE_INSTALL_PREFIX=/app/mysql57\

-DMYSQL_DATADIR=/data/mysql56/data/\

-DDOWNLOAD_BOOST=1\

-DWITH_BOOST=../boost_1_59_0\

-DSYSCONFDIR=/etc\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DWITH_FEDERATED_STORAGE_ENGINE=1\

-DWITH_BLACKHOLE_STORAGE_ENGINE=1\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DENABLED_LOCAL_INFILE=1\

-DENABLE_DTRACE=0\

-DDEFAULT_CHARSET=utf8mb4\

-DDEFAULT_COLLATION=utf8mb4_general_ci\

-DWITH_EMBEDDED_SERVER=1

Make-j `grep processor / proc/cpuinfo | wc-l`

Make install

Cp / app/mysql57/support-files/mysql.server / etc/init.d/mysqld57

5. Create a profile

/ etc/my.cnf, for reference only (DB1 configuration)

[client]

Port = 3306

Socket = / data/mysql57/mysql.sock

[mysql]

# prompt= "(\ u:HOSTNAME:) [\ d] >"

Prompt= "\ u @\ h\ R:\ m:\ s [\ d] >"

No-auto-rehash

User=root

Password=system

[mysqld]

# user = nobody

Port = 3306

Socket = / data/mysql57/mysql.sock

Basedir = / app/mysql57

Datadir = / data/mysql57/data

Character-set-server = utf8mb4

Skip_name_resolve = 1

Open_files_limit = 3072

Back_log = 103

Max_connections = 512

Max_connect_errors = 100000

Table_open_cache = 512

External-locking = FALSE

Max_allowed_packet = 32m

Sort_buffer_size = 2m

Join_buffer_size = 2m

Thread_cache_size = 51

Query_cache_size = 0

Query_cache_type = 0

# default_table_type = InnoDB

Tmp_table_size = 96m

Max_heap_table_size = 96m

Slow_query_log = 1

Slow_query_log_file = / data/mysql57/slow.log

Log-error = / data/mysql57/error.log

Long_query_time = 0.1

Server-id = 2134

Log-bin = / data/mysql57/data/bin_log

Sync_binlog = 1

Binlog_cache_size = 4m

Max_binlog_cache_size = 8m

Max_binlog_size = 1024m

Expire_logs_days = 7

Master_info_repository = TABLE

Relay_log_info_repository = TABLE

# gtid_mode = on

# enforce_gtid_consistency = 1

Log_slave_updates

Binlog_format = row

Relay_log_recovery = 1

Key_buffer_size = 32m

Read_buffer_size = 1m

Read_rnd_buffer_size = 16m

Bulk_insert_buffer_size = 64m

Myisam_sort_buffer_size = 128m

# myisam_max_sort_file_size = 10G

# myisam_max_extra_sort_file_size = 10G

Myisam_repair_threads = 1

# myisam_recover

Lock_wait_timeout = 300

Innodb_thread_concurrency = 0

Transaction_isolation = REPEATABLE-READ

# innodb_additional_mem_pool_size = 16m

Innodb_buffer_pool_size = 717m

Innodb_buffer_pool_load_at_startup = 1

Innodb_buffer_pool_dump_at_shutdown = 1

Innodb_data_file_path = ibdata1:1024M:autoextend

Innodb_flush_log_at_trx_commit = 1

Innodb_log_buffer_size = 16m

Innodb_log_file_size = 2G

Innodb_log_files_in_group = 2

Innodb_io_capacity = 4000

Innodb_io_capacity_max = 8000

Innodb_max_dirty_pages_pct = 30

Innodb_flush_method = O_DIRECT

Innodb_file_format = Barracuda

Innodb_file_format_max = Barracuda

Innodb_lock_wait_timeout = 10

Innodb_rollback_on_timeout = 1

Innodb_print_all_deadlocks = 1

Innodb_file_per_table = 1

Innodb_locks_unsafe_for_binlog = 0

Auto_increment_increment=2

Auto_increment_offset=1

Lower_case_table_names = 1

[mysqldump]

Quick

Max_allowed_packet = 32m

User=root

Password=system

DB2 / etc/my.cnf configuration

[client]

Port = 3306

Socket = / data/mysql57/mysql.sock

[mysql]

# prompt= "(\ u:HOSTNAME:) [\ d] >"

Prompt= "\ u @\ h\ R:\ m:\ s [\ d] >"

No-auto-rehash

User=root

Password=system

[mysqld]

# user = nobody

Port = 3306

Socket = / data/mysql57/mysql.sock

Basedir = / app/mysql57

Datadir = / data/mysql57/data

Character-set-server = utf8mb4

Skip_name_resolve = 1

Open_files_limit = 3072

Back_log = 103

Max_connections = 512

Max_connect_errors = 100000

Table_open_cache = 512

External-locking = FALSE

Max_allowed_packet = 32m

Sort_buffer_size = 2m

Join_buffer_size = 2m

Thread_cache_size = 51

Query_cache_size = 0

Query_cache_type = 0

# default_table_type = InnoDB

Tmp_table_size = 96m

Max_heap_table_size = 96m

Slow_query_log = 1

Slow_query_log_file = / data/mysql57/slow.log

Log-error = / data/mysql57/error.log

Long_query_time = 0.1

Server-id = 2135

Log-bin = / data/mysql57/data/bin_log

Sync_binlog = 1

Binlog_cache_size = 4m

Max_binlog_cache_size = 8m

Max_binlog_size = 1024m

Expire_logs_days = 7

Master_info_repository = TABLE

Relay_log_info_repository = TABLE

# gtid_mode = on

# enforce_gtid_consistency = 1

Log_slave_updates

Binlog_format = row

Relay_log_recovery = 1

Key_buffer_size = 32m

Read_buffer_size = 1m

Read_rnd_buffer_size = 16m

Bulk_insert_buffer_size = 64m

Myisam_sort_buffer_size = 128m

# myisam_max_sort_file_size = 10G

# myisam_max_extra_sort_file_size = 10G

Myisam_repair_threads = 1

# myisam_recover

Lock_wait_timeout = 300

Innodb_thread_concurrency = 0

Transaction_isolation = REPEATABLE-READ

# innodb_additional_mem_pool_size = 16m

Innodb_buffer_pool_size = 717m

Innodb_buffer_pool_load_at_startup = 1

Innodb_buffer_pool_dump_at_shutdown = 1

Innodb_data_file_path = ibdata1:1024M:autoextend

Innodb_flush_log_at_trx_commit = 1

Innodb_log_buffer_size = 16m

Innodb_log_file_size = 2G

Innodb_log_files_in_group = 2

Innodb_io_capacity = 4000

Innodb_io_capacity_max = 8000

Innodb_max_dirty_pages_pct = 30

Innodb_flush_method = O_DIRECT

Innodb_file_format = Barracuda

Innodb_file_format_max = Barracuda

Innodb_lock_wait_timeout = 10

Innodb_rollback_on_timeout = 1

Innodb_print_all_deadlocks = 1

Innodb_file_per_table = 1

Innodb_locks_unsafe_for_binlog = 0

Auto_increment_increment=2

Auto_increment_offset=2

Lower_case_table_names = 1

[mysqldump]

Quick

Max_allowed_packet = 32m

User=root

Password=system

6. Initialize the database

/ app/mysql57/bin/mysqld-initialize-insecure-user=mysql-basedir=/app/mysql57-datadir=/data/mysql57/data

Note:-- initialize-insecure will not generate random passwords. It is recommended to run security scripts after installation.

7. Modify environment variabl

Echo "export PATH=$PATH:/app/mysql57/bin" > / etc/profile.d/mysql57.sh

Source / etc/profile.d/mysql57.sh

/ etc/init.d/mysqld57 start

three。 Configure replication

1.db01 configuration

CREATE USER 'repl'@'%' IDENTIFIED BY' 12345678'

GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%'

2.db02 configuration

CREATE USER 'repl'@'%' IDENTIFIED BY' 12345678'

GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%'

3. Two mainframe lock libraries

FLUSH TABLES WITH READ LOCK

Db01 hosts view master pos

Root@localhost 23:21: [(none)] > show master status\ G

* * 1. Row *

File: bin_log.000009

Position: 2810194

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 5311bf4f-abe4-11e6-9732-000c29c7d527:1-3

1 row in set (0.00 sec)

Db02 hosts view master pos

Root@localhost 19:16: [(none)] > show master status\ G

* * 1. Row *

File: bin_log.000010

Position: 194

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 33655518-a3b3-11e6-8e89-000c29635439:1-3

1 row in set (0.00 sec)

Db01,db02 operates separately

Unlock Tables

Db02 operation

CHANGE MASTER TO MASTER_HOST = '192.168.213.4, MASTER_USER =' repl', MASTER_PASSWORD = '12345678, MASTER_LOG_FILE =' bin_log.000009', MASTER_LOG_POS = 2810194

Start slave

Db01 operation

CHANGE MASTER TO MASTER_HOST = '192.168.213.5, MASTER_USER =' repl', MASTER_PASSWORD = '12345678, MASTER_LOG_FILE =' bin_log.000010', MASTER_LOG_POS = 194

Start slave

Db02:

Root@localhost 19:19: [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.213.4

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: bin_log.000009

Read_Master_Log_Pos: 2810194

Relay_Log_File: ORADB-213-5-relay-bin.000036

Relay_Log_Pos: 318

Relay_Master_Log_File: bin_log.000009

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 2810194

Relay_Log_Space: 531

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: 2134

Master_UUID: 5311bf4f-abe4-11e6-9732-000c29c7d527

Master_Info_File: mysql.slave_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: 33655518-a3b3-11e6-8e89-000c29635439:1-3

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

Db01:

Root@localhost 23:21: [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.213.5

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: bin_log.000010

Read_Master_Log_Pos: 194

Relay_Log_File: ORA11G-213-4-relay-bin.000025

Relay_Log_Pos: 318

Relay_Master_Log_File: bin_log.000010

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 194

Relay_Log_Space: 741

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: 2135

Master_UUID: 33655518-a3b3-11e6-8e89-000c29635439

Master_Info_File: mysql.slave_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: 5311bf4f-abe4-11e6-9732-000c29c7d527:1-3

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

At this point, I believe you have a deeper understanding of "how to achieve mysql 5.7master synchronization". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.