How To Multiple Databases Replication On Ubuntu 16.049 min read
Mysql database replication is very important issue for disaster recovery. So you can configure step by step multiple database replication on Ubuntu 16.04. I am ready to help some tutorial for you. Please make sure that, your server must be ssh enable without password authentication.
★ How To Install Mysql Server on Ubuntu 14.04
★ How To Install mysql Community on Windows 10
Install mysql server for both ubuntu 16.04 server.
root@Master-Serv:~# apt -y install mysql-server
Mysql master slave replication on ubuntu 16.04 prerequisite
- Must be enable ssh authentication without password
- Must be ufw and any firewall disable for two server
- Must be comment out the below lines master and slave server
Master Server IP : 10.66.100.20
Slave Server IP : 10.66.100.21
root@Master-Serv:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf root@Slave-Serv:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf
Must be comment below line.
#bind-address = 127.0.0.1 #skip-networking [If exit or not]
Master Server configuration on ubuntu 16.04
Step #01: Open my.cnf file then insert server id, relay log and log bin file location.
root@Master-Serv:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf
Insert below all lines
[mysqld] server-id= 1 relay-log=/var/log/mysql/mysql-relay-bin.log log-bin=/var/log/mysql/mysql-bin.log
now mysql restart command
root@Master-Serv:~# service mysql restart
Step #02: Create replication user with grant privileges.
root@Master-Serv:~# mysql -u root -p
Then type mysql root password
mysql> CREATE database test_db; mysql> CREATE USER 'replica'@'%' IDENTIFIED BY 'passw0rd'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'replica'@'%'; mysql> FLUSH PRIVILEGES; mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000160 | 35117 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> FLUSH TABLES WITH READ LOCK;
Step #03: Open another terminal for backup all master database.
root@Master-Serv:~# mysqldump -u root -p --all-databases --master-data > masterdb.sql
Copy masterdb.sql file into slave server
root@Master-Serv:~# scp masterdb.sql root@slave-server-ip:/root/
Slave Server configuration on ubuntu 16.04
root@Slave-Serv:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf
Insert below all lines
[mysqld] server-id= 2 relay-log=/var/log/mysql/mysql-relay-bin.log log-bin=/var/log/mysql/mysql-bin.log
now mysql restart command
root@Slave-Serv:~# service mysql restart
Step #04: Now database import into slave server.
root@Slave-Serv:~# mysql -u root -p < masterdb.sql
Type your slave server root password. Wait at lest few minutes.
Goto mysql terminal command below:
root@Slave-Serv:~# mysql -u root -p
Again type your slave server root password.
mysql> stop slave; mysql> change master to master_host='master-server-ip', master_user='replica', master_password='replica-user-password', master_log_file='file-value-from-master', master_log_pos=log-position_from-master; mysql> start slave;
Step #05: Goto master terminal when first time login then unlock tables command below.
mysql> UNLOCK TABLES;
Step #06: Now check master slave replication status.
Goto slave server mysql terminal then run below command.
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.66.100.20
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000160
Read_Master_Log_Pos: 153024467
Relay_Log_File: mysql-relay-bin.000020
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000160
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: 35117
Relay_Log_Space: 154
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: 2003
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID: c8d666a2-ff5e-11e7-9e40-eebc113755c2
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 180417 11:54:56
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
Step#07: Replication test create table and then goto slave server.
Goto master server mysql terminal
mysql> use test_db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create table test (c int); Query OK, 0 rows affected (0.60 sec) mysql> show tables; +----------------------+ | Tables_in_test | +----------------------+ test +----------------------+ 1 rows in set (0.00 sec)
Now goto slave server mysql terminal
mysql> use test_db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------------+ | Tables_in_test | +----------------------+ test +----------------------+ 1 rows in set (0.00 sec)
If any problem so please see my YouTube channel for more information. Please share this tutorial any social media.
I am system administrator as Windows and Linux platform. I have 4 years skilled from the professional period. I have to configure Linux based system such as an Asterisk VOIP system, Network monitoring tools (ZABBIX), Virtualization (XEN Server), Cloud computing (Apache CloudStack) etc. Now share my professional skill each interested person.
Thanks to all.
Thanks to all.
Latest posts by Admin (see all)
- How To Install Zabbix Server 6.4 On AlmaLinux 8 Server - August 9, 2024
- How To Clear RAM Memory Cache Buffer Linux Server - February 29, 2024
- How To Install Apache Cloud Stack Management Server 4.17 On Ubuntu 22.04 - February 1, 2024
We really appreciate for your support for buying me a coffee.
It takes us a while to compose and upload new content, as they have to be checked to see if Properly works. We appreciate you if you help me for one cup of coffee to keep us awake and always deliver good quality content.
No contribution is small of an amount. We are grateful for any amount you support us with. Thank you!
