Mysql is database management system on such as windows, linux or mac system. Data Replication is a very important issue because the server may be lost at any time. So your important data will not be returned again. But if you have a data replication, then you can get back there.

Install ubuntu 14.04 into your virtual machine.

★ How To Install mysql Community on Windows 10

★ How To Install Laravel On Ubuntu 16.04 With Phpmyadmin and Mysql

At first install mysql server on ubuntu 14.04. To send data from server to server, authentication is not required. ‍So second step enable ssh without password authentication.
Now I will have to show mysql master slave replication on your ubuntu 14.04 server.

Mysql-Master ip : 10.66.20.1

Mysql-Slave ip : 10.66.20.2

Master Mysql Server configuration.

root@Mysql-Master:~# apt -y install mysql-server

Step #01: Collect your master server IP address and created master server id use any id. 

Master Server IP address 10.66.20.1

Step #02: Open my.cnf file any editor.

root@Mysql-Master:~# vi /etc/mysql/my.cnf

Comment out two lines into my.cnf file or find /etc/mysql/my.cnf.d/mysql.conf file.

#bind-address = 127.0.0.1
#skip-networking

Step #03: Insert my all databases name and server id into my.cnf file under [mysqld] section.

Open vi /etc/mysql/my.cnf

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog-do-db = master_db1
binlog-do-db = master_db2

Then restart you mysql server

root@Mysql-Master:~# /etc/init.d/mysql restart

Step #04: Create two databases previously inserted my.cnf file.

Goto mysql -u root -p for create database.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql> create database master_db1;
Query OK, 1 row affected (0.00 sec)

mysql> create database master_db2;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master_db1         |
| master_db2         |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
mysql>

Step #05: Create table into my created databases.

mysql> use master_db1;
Database changed
mysql> create table master_table1 (c int);
Query OK, 0 rows affected (0.01 sec)

mysql> use master_db2;
Database changed
mysql> create table master_table2 (c int);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------------+
| Tables_in_master_db2 |
+----------------------+
| master_table2        |
+----------------------+
1 row in set (0.00 sec)

mysql> use master_db1;
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_master_db1 |
+----------------------+
| master_table1        |
+----------------------+
1 row in set (0.00 sec)
mysql>

Step #06: New user needed for database replication.

I am create user “slave” for DB replication into master and slave server.

mysql> create user 'slave'@'10.66.20.2' identified by 'passw0rd';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'slave'@'10.66.20.2' identified by 'passw0rd';
mysql> grant all privileges on *.* to 'slave'@'%' identified by 'passw0d';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Step #07: When database created complete so I can stop table read and show master status.

Collect  file name and log position for configure slave server.

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+-----------------------+------------------+
| File             | Position | Binlog_Do_DB          | Binlog_Ignore_DB |
+------------------+----------+-----------------------+------------------+
| mysql-bin.000001 |      866 | master_db1,master_db2 |                  |
+------------------+----------+-----------------------+------------------+
1 row in set (0.00 sec)

mysql>

Step #08: Open another ssh session for export Master server DB.

root@Mysql-Master:~# mysqldump -uroot -ppassw0rd --databases master_db1 master_db2 > two_databases.sql

Transfer exported database from master server to slave server.

root@Mysql-Master:~# scp two_databases.sql root@your_slave_server_ip:/root/

Slave Mysql Server configuration.

root@Mysql-Slave:~# apt -y install mysql-server

Step #01: Slave server ip address 10.66.20.2

Import master server database into my slave server command below.

root@Mysql-Slave:~# mysql -uroot -ppassword < two_databases.sql

Step #02: Comment out two line and database name into my.cnf file as like master server configure.

Open /etc/mysql/my.cnf file or find /etc/mysql/my.cnf.d/mysql.conf via any editor.

root@Mysql-Slave:~# vi /etc/mysql/my.cnf

Comment below line.

#bind-address = 127.0.0.1
#skip-networking

Insert database name and server id into my.cnf file on [mysqld] section.

[mysqld]
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog-do-db = master_db1
binlog-do-db = master-db2

Restart mysql service command below

root@Mysql-Master:~# /etc/init.d/mysql restart

Step #03: When inserted DB name and server id into my.cnf file now got mysql console.

root@Mysql-Slave:~# mysql -uroot -p

Create slave user as like master server with password and grant privileges.

mysql> grant all privileges on *.* to 'slave'@'%'identified by 'passw0rd';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

Step #04: Connect master server with database file name and log position.

mysql> change master to master_host='Your_master_server_ip', master_user='slave', master_password='passw0rd', master_log_file='mysql-bin.000001', master_log_pos=866;
Query OK, 0 rows affected (0.01 sec)

mysql>

Return to previous master ssh session and follow this command for unlock table.

mysql> unlock tables;

Start slave command and then show slave status.

mysql> slave start;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting to reconnect after a failed registration on master
                  Master_Host: 10.66.20.1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 866
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            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: 866
              Relay_Log_Space: 107
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1597
                Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'slave'@'10.34.20.2' (using password: YES) (Errno: 1045)
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

I can see some Error “Master command COM REGISTER_SLAVE faild: Access denied for user 

Fixing the problem is actually quite easy. Skip the invalid query.
set global sql_slave_skip_counter=1;

stop slave and run command below.

mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)

mysql>

Then goto master server and run command below.

mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)

mysql>

Now fix my error message run command show slave staus.

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.66.20.1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1112
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 499
        Relay_Master_Log_File: mysql-bin.000001
             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: 1112
              Relay_Log_Space: 656
              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)

ERROR:
No query specified

mysql>

Step #05: Test my master and slave server replication.

Goto master server run some command such as create table as like below command.

mysql> use master_db1;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_master_db1 |
+----------------------+
| master_table1        |
+----------------------+
1 row in set (0.00 sec)
mysql> create table test (c int);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------------+
| Tables_in_master_db1 |
+----------------------+
| master_table1 |
| test |
+----------------------+
2 rows in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql>

And goto slave server and run some command for checking database replication.

mysql> use master_db1;
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_master_db1 |
+----------------------+
| master_table1        |
+----------------------+
1 row in set (0.00 sec)

mysql> show tables;
+----------------------+
| Tables_in_master_db1 |
+----------------------+
| master_table1        |
| test                 |
+----------------------+
2 rows in set (0.00 sec)

mysql> show tables;
+----------------------+
| Tables_in_master_db1 |
+----------------------+
| master_table1        |
+----------------------+
1 row in set (0.00 sec)

mysql>

If you can monitor mysql server then see how to monitor mysql server from zabbix .

• Author
• Recent Posts

Admin

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.

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