MySQL master-slave Replication-MyIsam and InnoDB Data Replication release

MyIsam is relatively simple to publish, just copy all the files in the library. The procedure of copying Innodb is similar to that of MyIsam, except that the table content in the Innodb Engine no longer exists in the same database directory (MyIsam does this). In the Innodb engine, data files are stored in files such as ibdata1, and operation logs are stored in files such as ib_logfile0, ib_logfile1, and ib_logfile2 by default.
Innodb replication and publishing are the following steps (MyIsam is simpler, remove the Innodb Section highlighted in particular, that is, the replication and publishing of MyIsam ):
1. Execute the flush tables with read lock Statement on the master database to clear all TABLES and block write statements: (you can also stop the database)
Mysql> flush tables with read lock;

2. Obtain the current record point of the master database.
Mysql> show master status \ G;
* *************************** 1. row ***************************
File: mysql-netprws_test-bin.000010
Position: 54736
Binlog_Do_DB: netprws
Binlog_Ignore_DB:
1 row in set (0.00 sec)


3. Copy data files from the master server to the slave server.
If the innodb engine is available, copy the innodb data file. The file name may be ibdata1.

4. master database: confirm that the file has been generated and copied.
Mysql> unlock tables;


Slave Server:
1. Stop the server used for the slave server and add the following lines to its my. cnf file:
[Mysqld]
Server-id = slave_id
Slave_id distinguishes Master_id values. It must be a positive integer between 2 and 2 ^ 32-1.

2. Start the slave server

3. Set master server information on slave server
Mysql> CHANGE MASTER
-> MASTER_HOST = 'master _ host_name ',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'replication _ user_name ',
-> MASTER_PASSWORD = 'replication _ password ',
-> MASTER_LOG_FILE = 'recorded _ log_file_name ',
-> MASTER_LOG_POS = recorded_log_position;

4. Start Replication
Mysql> start slave;

5. view the copy status
Mysql> show slave status;
Mysql> show processlist;

In the case of Innodb:
Last_errno.: 1033
The error here is caused by the replication and publishing of innodb. You need to delete the log file of the original innodb from the database. The file name is usually ib_logfile0, ib_logfile1...

Restart the slave database.

Mysql> show slave status \ G;
......

Master_Log_File: mysql-netprws_test-bin.000010
Read_Master_Log_Pos: 55786
Relay_Log_File: localhost-relay-bin.000018
Relay_Log_Pos: 55936
Relay_Master_Log_File: mysql-netprws_test-bin.000010
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

......

The two have NO. They are generally caused by Master_Log_File or Read_Master_Log_Pos settings, and there is a network problem. Generally, it should be okay here. If there is still a problem, locate it based on the error code and error prompt displayed in show slave status \ G.

In addition, we recommend that you use one of the settings in my. cnf of the slave database.
Read_only

It is necessary to enable this attribute to prevent the slave Database Synchronization failure caused by many manual modifications to the slave database.

The binlog method is more flexible and labor-saving, and supports Incremental backup.

Mysqld must be restarted when binlog is enabled. First, close mysqld, open my. cnf, and add the following lines:

Server - Id = 1
　　 Log - Bin = Binlog
　　 Log - Bin - Index = Binlog. Index

Start mysqld. Binlog.000001 and binlog. index will be generated during the running process. The previous file is the update operation of mysqld to record all data, and the subsequent file is the index of all binlogs, which cannot be easily deleted. For information about binlog, see the manual.

When you need to back up data, you can execute an SQL statement to stop mysqld from writing data to the current binlog, and then back up the file directly. This will achieve the purpose of Incremental Backup: flush logs; if it is a slave server in the backup replication system, you should also back up master.info and relay-log.info files. The binlog file backed up can be viewed using mysqlbinlog, a tool provided by MySQL, for example: /usr/local/mysql/bin/mysqlbinlog/tmp/binlog.000001 this tool allows you to display all SQL statements in the specified database, and you can also set a time range, which is quite convenient, for more information, see the manual.

You can use a statement similar to the following to restore data: /usr/local/mysql/bin/mysqlbinlog/tmp/binlog.000001 | mysql-uyejr-pyejr db_name runs the SQL statement output by mysqlbinlog as input.