MySQL master-slave Replication configuration Demo

node1:192.168.139.2 Primary Server

node2:192.168.139.4 from the server

First, install MySQL on Node1 and Node2, which does not show

The following configuration is required for the master and slave:

Master

1. Enable binary logging

   Log_bin = Master-bin Log_bin_index = Master-bin.index

2. Select a unique server ID

   server_id = {0-2^32-1}

3. Create a user with copy permissions that is specifically used to replicate

   Replication slave has permission to replicate events from the binary log on the primary server

   Replication client has permission to connect to the primary server to obtain relevant information (this permission is not required)

Slave

1. Start the relay log and turn off the binary log

   Relay_log = Relay-log Relay-log-index = Relay-bin.index

2. Select a unique server ID

   server_id = {0-2^32-1}

3. Connect to the master server and start copying data

   
   

   mysql> Change Master to master_host= ' 192.168.139.2 ', master_user= ' Faker ', master_password= ' 123 ', master_log_file= ' Master-bin.000004 ', master_log_pos=579;

   mysql> start slave; start a copy thread from MySQL

The following is a configuration demo

[Email protected] mysql]# VIM/ETC/MY.CNF

[Mysqld]

Datadir=/mydata/data

innodb_file_per_table = 1

Log-bin = Master-bin

Log-bin-index = Master-bin.index

Binlog_format=mixed

server_id = 1

[[Email protected] mysql]# service mysqld start

Starting MySQL ....... success!

[Email protected] mysql]# mysql-uroot-p

Enter Password:

mysql> create user [email protected] ' 127.0.0.1 ' identified by ' 123 ';

Query OK, 0 rows affected (0.08 sec)

mysql> grant replication Slave on * * to ' faker ' @ ' 192.168.139.% ' identified by ' 123 ';

Query OK, 0 rows affected (0.05 sec)

mysql> flush Privileges;

Query OK, 0 rows affected (0.03 sec)

Mysql> Show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+-------------------+----------+--------------+------------------+-------------------+

|    master-bin.000004 |        579 |              | |

+-------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.00 sec)

Mysql> \q

Bye

[Email protected] mysql]# VIM/ETC/MY.CNF

[Mysqld]

Datadir=/mydata/data

Innodb_file_per_table = On

#log-bin = Master-bin

#log-bin-index = Master-bin.index

Relay-log = Relay-log

Relay-log-index = Relay-bin.index

server_id = 11

[[Email protected] mysql]# service mysqld start

Starting MySQL ......... ....... success!

[Email protected] mysql]# mysql-uroot-p

Enter Password:

mysql> Change Master to master_host= ' 192.168.139.2 ', master_user= ' Faker ', master_password= ' 123 ', master_log_file= ' Master-bin.000004 ', master_log_pos=579;

Query OK, 0 rows affected, 2 warnings (0.23 sec)

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:

master_host:192.168.139.

Master_user:faker

master_port:3306

Connect_retry:60 If you cannot connect to the primary server, try again after 60

MASTER_LOG_FILE:MASTER-BIN.000004 the current read primary server binary log

read_master_log_pos:579 the current read primary server binary log location

relay_log_file:relay-log.000001 the trunk log that is now used (can be found to be inconsistent with the primary server)

Relay_log_pos:4 the event location in the current trunk log

RELAY_MASTER_LOG_FILE:MASTER-BIN.000004 The primary server binary log that is synchronizing

Slave_io_running:no the IO thread from the server is not enabled

Slave_sql_running:no SQL thread from the server is not enabled

REPLICATE_DO_DB: The next few are for replication filtering

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 skipped 0 of them.

EXEC_MASTER_LOG_POS:579 is currently executing in that position.

relay_log_space:120

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:null How many seconds are slower from the server than the primary server

Master_ssl_verify_server_cert:no

last_sql_errno:0

Last_sql_error:

Replicate_ignore_server_ids:

master_server_id:0

Master_uuid:

Master_info_file:/mydata/data/master.info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:

master_retry_count:86400

Master_bind:

Last_io_error_timestamp:

Last_sql_error_timestamp:

MASTER_SSL_CRL:

Master_ssl_crlpath:

Executed_gtid_set:

auto_position:0

1 row in Set (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.07 sec)

Main:

mysql> CREATE DATABASE ZXL;

Query OK, 1 row affected (0.51 sec)

Mysql>

From:

mysql> show databases;

+--------------------+

| Database

+--------------------+

| Information_schema |

| Mysql

| Performance_schema |

| Test

| ZXL |

+--------------------+

5 rows in Set (0.00 sec)

How to make read-only from the server

[Email protected] data]# VIM/ETC/MY.CNF

Read_Only = On

[Email protected] data]# service mysqld restart

Shutting down MySQL. success!

Starting MySQL ....... ..... success!

[Email protected] data]# mysql-uroot-p

Enter Password:

Mysql> show global variables like ' read_only ';

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| Read_Only | On |

+---------------+-------+

1 row in Set (0.12 sec)

Mysql> Show Slave Status\g

Slave_io_running:yes

Slave_sql_running:yes

It can be seen that the IO and SQL threads start automatically after MySQL reboot, but the IO thread will connect to the main server's dump thread to start, and to specify the user password, log event, etc., how does it start automatically?

[[Email protected] mysql]# file Master.info

Master.info:ASCIItext

[email protected] data]# cat Master.info

23

master-bin.000004

670

192.168.139.2 Main ServerIP

Faker users

123 Password

3306 ports

60 Retry Time

0

[email protected] data]# ll Master.info

-RW-RW----. 1 MySQL MySQL 15:53 master.info

[email protected] data]# cat Relay-log.info

7

./relay-log.000002 the trunk log currently in use

375 Log Location

master-bin.000004 binary Log currently read

6,702 Binary Log Location

0

0

1

The above two files Master.info and Relay-log.info Save the content required for the IO thread to start automatically

However, there may be accidental deletions on the primary server, which can take advantage of the time difference on master-slave replication, immediately stop replication from the server, and retrieve data from the server that was mistakenly deleted on the primary server.

>stop slave; can stop SQL and IO threads directly

Percona also provides tools specifically for managing master-slave replication:

[email protected] data]# Yum install Percona-toolkit

A command that generates many PT starts after installation

Such as:

Pt-slave-delay can intentionally allow a certain amount of time from replication to be slower than the master, and errors can be remedied by

Pt-summary collects information such as CPU and memory size of the current server

Pt-diskstats display of magnetic IO statistics

Pt-index-usage Display Current Index usage

Pt-table-checksum check whether the data on the master and slave are consistent

Pt-ioprofile evaluating the IO capability of the current disk

This article is from the "11097124" blog, please be sure to keep this source http://11107124.blog.51cto.com/11097124/1898899

MySQL master-slave Replication configuration Demo