Configuration for Master Server (10.0.0.100)
In your /etc/my.cnf add the following:
server-id=1
log-bin=mysql-bin
port=3306
max_allowed_packet=256M
binlog_do_db=exampledb
expire_logs_days=20
max_binlog_size=500M
auto_increment_increment=2
auto_increment_offset=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
And run the following SQL command as the root user:
grant all on *.* to 'msandbox'@'10.0.0.101' identified by 'msandbox';
flush privileges;
Restart the MySQL server.
service mysqld restart
mysql -u root -p
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000017 | 66402 | billing_new | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Open a second terminal for Master Server, create the dump and transfer it to Slave Server.
Don’t leave the MySQL-shell at this point – otherwise you’ll loose the read-lock (If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.)
cd /tmp/
mysqldump -u root -p exampledb > sqldump.sql
scp sqldump.sql root@10.0.0.101:/tmp/
Afterwards close the second terminal and switch back to the first. Remove the read-lock and leave the MySQL-shell.
UNLOCK TABLES;
quit;
Configuration for Slave Server (10.0.0.101)
import the dump to slave server database.
mysql -u root -p
CREATE DATABASE exampledb;
exit;
mysql -umsandbox -p exampledb < /tmp/sqldump.sql
In your /etc/my.cnf add the following:
server-id = 2
log-bin=mysql-bin
binlog-do-db=exampledb
lower_case_table_names=1
Restart the MySQL server.
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='10.0.0.100',MASTER_USER='sandbox', MASTER_PASSWORD='msandbox', MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=66402;
START SLAVE;
SHOW SLAVE STATUS \G
MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.100
Master_User: sandbox
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000017
Read_Master_Log_Pos: 577942
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 578226
Relay_Master_Log_File: mysql-bin.000017
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: 577942
Relay_Log_Space: 578522
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: 0
Master_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
1 row in set (0.00 sec)
and take a look at the slave status. It’s very important that both, Slave_IO_Running and Slave_SQL_Running are set to Yes. If they’re not, something went wrong and you should take a look at the logs.
