Shulou(Shulou.com)06/01 Report--

First, the principle of MySQL replication.

Second, MySQL replication configuration.

I. the principle of MySQL replication

1.MySQL copy schematic

The principle of replication:

Slave starts IO Thread and SQL Thread

Master starts DumpThread

1.Slave sends a request to Master's Dump Thread through IO Thread, and Master's Dump Thread requests a local binlog.

2.Master reads the local binlog and sends the read to the IO Thread thread of Slave.

The IO Thread of 3.Slave writes the received content to the local relaylog.

4.Slave 's SQL Thread reads the contents of the local relaylog file.

The SQL Thread of 5.Slave writes the read to the local database.

II. MySQL replication configuration

1.MySQL redrawing

two。 Configure Master

2.1. Modify Master configuration file

Vim / etc/my.cnf [mysqld] # Open binary log file log-bin = mysql-bin# configuration unique server idserver-id = transaction security sync_master_info = 1sync_binlog = 1 innodb_support_xa = ON

Full text of 2.2.Master configuration file

[client] port = 3306socket = / tmp/ mysql.sock [mysqld] port = 3306socket = / tmp/mysql.sockskip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8queryroomcachesize= 16Mthread_concurrency = 8log-bin=mysql-binbinlog_format=mixedserver-id = 1sync_master_info = 1sync_binlog = 1 innodb_support_xa = ONdatadir = / Data/mysql/3306/datainnodb_data_home_dir = / data/mysql/3306/datainnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = / data/mysql/3306/datainnodb_buffer_pool_size = 256Minnodb_additional_mem_pool_size = 20Minnodb_log_file_size = 64Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 2innodb_lock_wait_timeout = 50innodb_file_per_table = ONskip_name_resolve = ON [ Mysqldump] quickmax_allowed_packet = 16m [MySQL] no-auto- rehash [myisamchk] key_buffer_size = 128Msort_buffer_size = 128Mread_buffer = 2Mwrite_buffer = 2m [mysqlhotcopy] interactive-timeout

2.3. Users who create replication permissions

MariaDB [(none)] > grant replication slave,replication client on *. * to 'repl'@'192.168.1.5' identified by' slavepass';Query OK, 0 rows affected (0.39 sec) MariaDB [(none)] > flush privileges;Query OK, 0 rows affected (0.06 sec)

3. Configure Slave

3.1. Modify Slave configuration file

Vim / etc/my.cnf [mysqld] # set unique IDserver-id = 3 # enable relay logrelay_log= relay-logrelay_log_index=relay-log.index# transaction security skip_slave_start = ONsync_relay_log = 1sync_relay_log_info = 1

Full text of 3.3.slave configuration file

[client] port = 3306socket = / tmp/ mysql.sock [mysqld] port = 3306socket = / tmp/mysql.sockskip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8queryroomcachesize= 16Mthread_concurrency = 8server-id = 3relayloglog= relay-logrelay_log _ index=relay-log.indexskip_slave_start = ONsync_relay_log = 1sync_relay_log_info = 1innodb_data_home_dir = / data/mysql/3306/datainnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = / data/mysql/3306/datainnodb_buffer_pool_size = 256Minnodb_additional_mem_pool_size = 20Minnodb_log_file_size = 64Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 2innodb_lock_wait _ timeout = 50innodb_file_per_table = ONskip_name_resolve = on [mysqldump] quickmax_allowed_packet = 16m [MySQL] no-auto- rehash [myisamchk] key_buffer_size = 128Msort_buffer_size = 128Mread_buffer = 2Mwrite_buffer = 2m [mysqlhotcopy] interactive-timeout

4. Start replication

4.1. View binlog Pos points on Master

MariaDB [(none)] > show master status\ gateway * 1. Row * * File: mysql-bin.000008 Position: 652Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)

4.2. Perform a synchronization operation on slave

MariaDB [(none)] > change master to master_host='192.168.1.4',master_user='repl',master_password='slavepass',master_log_file='mysql-bin.000008',master_log_pos=652;Query OK, 0 rows affected (0.93 sec)

4.3. Start slave on slave

MariaDB [(none)] > start slave;Query OK, 0 rows affected (0.02 sec)

4.3. View slave status on slave

MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.4 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 652 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 537 Relay_Master_Log_File: mysql-bin.000008 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: 652 Relay_Log_Space: 829 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: 0Master_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: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative1 row in set (0.00 sec)

5. Test synchronization

5.1. Create data in master

MariaDB [(none)] > create database ckldb;Query OK, 1 row affected (0.37 sec) MariaDB [(none)] > use ckldb;Database changedMariaDB [ckldb] > create table jone (id int,name varchar (30)); Query OK, 0 rows affected (0.29 sec) MariaDB [ckldb] > insert into jone values (1 row affected (0.49 sec) MariaDB [ckldb] > delete from jone Query OK, 1 row affected (0.09 sec) MariaDB [ckldb] > insert into jone values; Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [ckldb] > select * from jone +-+-+ | id | name | +-+-+ | 1 | wukaka | | 2 | side | +-+-+ 2 rows in set (0.00 sec)

5.2. View on slave

MariaDB [(none)] > show databases +-+ | Database | +-+ | ckldb | | information_schema | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.34 sec) MariaDB [(none)] > use ckldb;Database changedMariaDB [ckldb] > show tables +-+ | Tables_in_ckldb | +-+ | jone | +-+ 1 row in set (0.00 sec) MariaDB [ckldb] > select * from jone +-+-+ | id | name | +-+-+ | 1 | wukaka | | 2 | side | +-+-+ 2 rows in set (0.00 sec)

Note that if the main library has been running for a long time, it is best to back up the main library and record the binlog Pos point before synchronization. Import backup to slave library

And then restore from binlog Pos.

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.