Master Settings
Be sure to specify a unique server ID, the path for binary logs, and the database name for replication in the [mysqld] section:
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
replicate-do-db = testdb
Ensure that you have enough disk space for binary logs.
Add a replication user, under whose privileges replication will be performed. The "replication slave" privilege is sufficient:
mysql@master> GRANT replication slave ON "testdb".* TO "replication"@"192.168.1.102" IDENTIFIED BY "password";
Restart MySQL to apply the configuration changes:
root@master# service mysqld restart
If everything went successfully, the "show master status" command should display something like this:
mysql@master> SHOW MASTER STATUS\G
File: mysql-bin.000003
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:
The position value should increase as changes are made to the database on the master.
Replica Settings
Specify the server ID, the database name for replication, and the path to relay logs in the [mysqld] section of the configuration, then restart MySQL:
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
replicate-do-db = testdb
root@replica# service mysqld restart
Moving Data
Here we will need to lock the database for writing. To do this, you can either stop the applications or use the read_only flag on the master (note: this flag does not apply to users with the SUPER privilege). If we have MyISAM tables, also perform "flush tables":
mysql@master> FLUSH TABLES WITH READ LOCK;
mysql@master> SET GLOBAL read_only = ON;
Check the master's status with the "show master status" command and note the File and Position values (after successfully locking the master, they should not change):
File: mysql-bin.000003
Position: 98
Create a database dump, and after the operation is complete, unlock the master:
mysql@master> SET GLOBAL read_only = OFF;
Transfer the dump to the replica and restore the data from it.
Finally, start replication using the "change master to" and "start slave" commands and check if everything went well:
mysql@replica> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000003 ", MASTER_LOG_POS = 98;
mysql@replica> start slave;
The MASTER_LOG_FILE and MASTER_LOG_POS values are taken from the master.
Check the replication status with the "show slave status" command:
mysql@replica> SHOW SLAVE STATUS\G
This article was taken from habr and verified in practice.
Encountered the error [ERROR] Failed to initialize the master info structure
Solved by: RESET SLAVE;
The second problem - (InnoDB tables were not created) - was resolved by deleting the logs .. 0 ..01
If you need to exclude certain tables from replication, add them to the configuration:
replicate-wild-ignore-table=base.mw_words_positions
replicate-wild-ignore-table=base.mw_sotrudniki_log
Skip errors:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; this did not help me,
but adding to my.cnf
slave-skip-errors= <error code or all>
solved the problem.