MySQL configuration Master master and master-slave backup

MySQL master-slave backup Configuration instance

Scene:

1, the primary server 192.168.0.225, from the server 192.168.0.226. Where the data is already available on the master server.

2, the MySQL version on the master and slave server and the same installation configuration.

First, the principle of master-slave backup:

Each operation of the primary server database is recorded in the binary log file mysql-bin.xxx. The I/O thread from the server uses a private account to log on to the primary server to read the binary, and writes the contents of the file to its local trunk log relay-log file. The SQL thread from the server then executes the SQL statement based on the contents of the trunk log.

This requires that both servers have the same initial state.

Second, synchronous initial state:

1, the main server to synchronize the database lock, to avoid the synchronization changes:

> Usedatabase_name;>flush tables with read lock;

2. Use the Mysqldump tool to export data:

MYSQLDUMP-UROOT-PXXX database_name >database_name.sql

3. After the backup is complete, unlock the database:

>unlock tables;

4. Import the initial data from the database:

>CREATE DATABASE database_name;> usedatabase_name;>source database_name.sql; 

After doing this, the master and slave servers have the same initial state.

Third, master-slave synchronization settings:

1. Configuration from the database:

/ETC/MY.CNF main configuration is as follows:

Log-bin=mysql-bin                                 # Open binary log     Server-id       = 2                               # The primary database ID is 1 and cannot be the same. # # # # # # # # # # # replicate_wild_do_table=test.%                     logs after synchronizing from server       

Restart the MySQL service after the modification is complete.

2. View the primary server journal record location:

>show Master Status\g

The contents of the display are as follows:

1. Row ****************            file:mysql-bin.000001       # Current recorded log        position:80647293               # location recorded in log In    Set (0.00 sec)    

3. The primary server creates an account that allows data to be synchronized from the server:

'user_name '@'192.168.0.226'ahaii';      

4. Start the synchronization from the server:

> ChangeMaster                   to master_host='192.168.0.225',                             master_user='user_name  ', master_password='ahaii', master_log_file='mysql-bin.000001', master_log_pos=80647293;                

After configuring the above, restart the MySQL service from the server.

5. To see if the synchronization has been successfully turned on from the server:

>show slave Status\g

Shown below:

1. Row ***************************Slave_io_state:waitingForMaster to send event master_host:192.168.0.225Master_user:user_name master_port:3306Connect_retry:60master_log_file:mysql-bin.000001read_master_log_pos:1114relay_log_file:mysqld-relay-bin.000004 Relay_log_pos:1260 relay_master_log_file: Mysql-bin.000002 Slave_io_running:yes slave_sql_running:yes replicate_do_db: Replicate_ignore_db:replicate_do_table:replicate_ignore_table:replicate_wild_do_table:test.% replicate_wild_ignore_table:last_errno:0 last_error:skip_counter:0 Exec_master_log_pos:1114 Relay_log_space:1563 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:11 row in Set (0.00 sec)  

Where: The status of Slave_io_running and slave_sql_running is yes, indicating that the synchronization is successful.

You can now go to the test library on the master server to create the table to open the testing synchronization.

MySQL primary master backup configuration instance

A dual-master backup is implemented based on the configuration of the master-slave backup:

One, create an account for synchronization from the server:

>grant ...

Second, modify the master server MYSQL.CNF, join

replicate_wild_do_table=test.%relay_log=mysqld-relay-binlog-slave-updates=yes 

Third, record the file name and location from the server binary log:

>show Master Status\g

Four, the primary server to open the synchronization settings:

> ChangeMaster             to master_host='192.168.0.226',             master_user='user_name  ', master_password='ahaii', master_log_file='mysql-bin.000009'  

Then restart the Mysqld service.

This is the process of MySQL master-slave and Main-master setup.

===================

Iv. Record of doubts:

1, about the value of Binlog-format in the MY.CNF configuration file:

MySQL's Binlog-format is available in three formats: row, statement, and mixed

1.1. Row: line-based replication (row-based REPLICATE,RBP)

Advantages:

Binlog can not record the context-sensitive information of the executed SQL statement, only need to record what the record was modified to. So the Rowlevel log content will be very clear to record the details of each row of data modification. There are no stored procedures, or function, and trigger calls and triggers that cannot be copied correctly in certain situations

Disadvantages:

All executed statements when recorded in the log, will be recorded in each row of changes to record, which may produce a large number of log content, such as an UPDATE statement, modify multiple records, the Binlog in each of the changes will be recorded, resulting in Binlog log volume will be very large, In particular, when executing a statement such as ALTER TABLE, each record in the table is recorded in the log because of changes in its structure.

1.2. Statement: SQL statement-based replication (statement-based replicate, SBR)

Each SQL statement that modifies the database is recorded in Binlog.

Advantages:

There is no need to record changes in each row, reducing the Binlog log volume, saving IO and improving performance. (compared to how much performance and log volume The row can save, depending on the SQL case of the application, the log volume generated by the normal record modification or the insertion of the row format is less than the amount of log generated by statement, but given the conditional update operation and the whole table deletion, ALTER TABLE operations, the row format generates a large number of logs, so the amount of log generated will increase, as well as the IO performance issues, when considering whether to use the row format log should be followed according to the actual application. )

Disadvantages:

Because the records only execute statements, in order for these statements to run correctly on the slave, it is also necessary to record some information about each statement at the time of execution, so that all statements can be slave and executed at the master end. In addition to MySQL replication, like some specific function functions, slave can be consistent with master on a number of related issues (such as the Sleep () function, last_insert_id (), and user-defined functions (UDF) can cause problems ).

Statements that use the following functions cannot be copied:

* Load_file ()

* UUID ()

* USER ()

* Found_rows ()

* Sysdate () (unless the--sysdate-is-now option is enabled at startup)

At the same time in the insert ... SELECT produces more row-level locks than RBR.

1.3. Mixed: Mixed mode replication (mixed-based replicate,mbr)

The default mode for MySQL.

Mixed mode is a mixed use of the above two modes, the general statement modification using the statment format to save Binlog, such as some functions, statement can not complete the master-slave copy operation, the row format to save Binlog, MySQL differentiates the log form of the treated record according to each specific SQL statement executed, that is, choosing between statement and row. The new version of the MySQL Squadron row level mode is also optimized, and not all changes are recorded at the row level. The statement pattern is recorded when a table structure change is encountered. For statements that modify data such as update or delete, the changes are recorded for all rows.

2, about the replication parameters: Replicate-do-db, replicate-do-table, replicate-wild-do-table

Description of MySQL website: https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html

2.1, REPLICATE-DO-DB: Set the database to be synchronized from the server, to synchronize multiple libraries, you can write multiple lines, one per line.

Disadvantage: Does not apply to replication of cross-Library data updates.

2.2, Replicate-do-table: Set the table to be synchronized from the server, the format is db_name.tb_name. One per line, multiple rows are required to synchronize more than one table.

Disadvantage: Does not apply to replication of cross-Library data updates.

2.3, Replicate-wild-do-table: Set the database to be synchronized from the server, the format is db_name.tb_name. One per line, multiple rows are required to synchronize more than one table. You can use wildcards such as "%", "_" to match multiple tables, such as all tables that begin with all use strings that synchronize all databases beginning with the share string: replicate-wild-do-db=share%.user%.

Supports replication of cross-Library data updates.

MySQL configuration Master master and master-slave backup