MySQL Master > Slave Replikation

Im Durchschnitt wird dieses Tutorial MySQL Master > Slave Replikation mit 5 bewertet, wobei 1.0 die schlechteste und 5.0 die beste Bewertung ist. Es haben insgesamt 377 Besucher eine Bewertung abgegeben.
376 1

MySQL Master > Slave Replikation

Datenbank MySQL Ubuntu
Set up a MySQL Master > Slave replication on Ubuntu

In the following article, I will show you how to install a MySQL database on an Ubuntu 16.04 server and set up a replication from a master to the slave.

One should however consider or note:
Replication is asynchronous! The slave server lags behind the master server, depending on the load, and data loss may occur.
The guide describes an unencrypted replication. It should be configured ONLY in private networks and under no circumstances in public networks (on the Internet)!
Remember: Replication is not a backup! If you delete a database on the master, it will be deleted on the slave as well!

case studies

Load distribution of reading queries

Since e.g. In many web applications a database is read by more than is written, one can divide the incoming load with a replication on several servers. (Scaling)

Master server relief

Replication can be used to run regular backups on the slave server, so you do not affect the capacity of the master server by the backup process.

Data distribution / disaster recovery

The data of one MySQL server can be replicated to another remote server in another datacenter. (Disaster recovery strategy)

Network Configuration

Because we need an internal network for database replication, I would like to briefly refer to the following tutorial how you set up an internal network at gridscale.io.

How to gridscale (german only)

Example konfiguration of IP addresses:

Server 1 (MASTER): 10.0.0.1
Server 2 (SLAVE): 10.0.0.2

Installation

Installation and start of MySQL-servers for server1 und server2:

apt-get -y install mysql-server
service mysql start

Preparation / Configuration

Open the MySQL command line and create a replication account:

mysql –u root –p
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘replication‘@’10.0.0.2‘ IDENTIFIED BY ‘password’;

Explanation:
replication = the account that will be used for replication
10.0.0.2 = address of the slave server
password = Please change this to a secure password

REPLICATION SLAVE:
The authorization „REPLICATION SLAVE“ is for the account on the slave server. With this it is able to connect to the master server and start the replication. Without this authorization, the slave can not request updates that were made on the databases on the master server.

REPLICATION CLIENT:
The “REPLICATION CLIENT” permission allows the use of “SHOW MASTER STATUS” and “SHOW SLAVE STATUS”

For more information on the commands and options used, please refer to the following MySQL documentation:

Englisch: LINK

Next, we make changes to the configuration of the “MASTER” server:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

The following entries must be checked / modified / accom- modated:

bind-address		= 10.0.0.1
server-id		= 1
log_bin			= /var/log/mysql/mysql-bin.log
max_binlog_size		= 100M

For control purposes, you can activate logging – but this is not recommended permanently, since the option is a performance killer!

general_log_file		= /var/log/mysql/mysql.log
general_log		= 1

– You can check the logfile later with the command: tail -f /var/log/mysql/mysql.log

After changing the configuration, we reload the configuration of the MySQL server, open the MySQL command line and look at the status of the “MASTER” server.

service mysql reload
mysql –u root –p
show master status;

Output:

mysql> show master status;
+---------------------+----------+----------------+---------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+----------------+---------------------+
| mysql-bin.000001  |      98    |                      |                            | 
+---------------------+----------+----------------+---------------------+

Remember the File & Position for the configuration of the SLAVE server!

Now we edit the configuration of the slave server:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

The following entries must be checked / modified / accom- modated:

server-id		= 2		# Must have an higher score than MASTER.
bind-address		= 10.0.0.2
log_bin			= /var/log/mysql/mysql-bin.log
max_binlog_size		= 100M

Again, you can active log files if you need to:

general_log_file		= /var/log/mysql/mysql.log
general_log		= 1

Create a Backup and import to SLAVE

On the “MASTER” Server you create the Backup with the following command:

mysqldump -A --allow-keywords --single-transaction -F --master-data=2 -r /root/mysqldump.sql --user=root -p

Explanation of the used options:

-A (–all-databases) Includes all databases and tables.

-F (–flush-logs) Synchronizes the log files of the MySQL server to the hard disk before the backup process is started.

–allow-keywords Allows the creation of keywords as column names.

–single-transaction This option sets a BEGIN SQL statement before the memory is pulled out from the server. The memory dump reflects the consistent state of the database at the time BEGIN was dispatched without locking applications.

–master-data=2 Includes File names and position of the binary log files to the output.

-r /root/dump.sql (–result-file=file) The output is issued into the file.

–user=root Use the following user name for this action.

-p (–password[=password]) Since no password is passed, we are asked by the server for a password.

For more information on the commands and options used, please refer to the following MySQL documentation:

LINK

The created Backup of the database can be found here /root/mysqldump.sql, this file needs to be transfered to Slave with the following command:

scp /root/mysqldump.sql root@10.0.0.2:/root/

If you want to know more about the usage of scp, you can read more about it in this tutorial: How to transfer Files via SCP (german only)

The file “mysqldump.sql” should be found now in the folder “/root/” on the slave server.

On Slave Server:

After successfully transferring the backup to the slave, we open the MySQL command line and delete all existing MySQL databases.

mysql -u root -p	# Open MySQL Command line tool
show databases;		# List all existing databases

The output should look something like that:

mysql> show databases;
+---------------------------+
| Database                  |
+---------------------------+
| mysql			    |
| test            	    |
+---------------------------+
2 rows in set (0.01 sec)

Now delete all databases with the following commands:

drop database mysql;
drop database test;
…
…
…

After all databases were deleted, exit the mysql console and return to the linux shell:

exit;

As the next step, you need to restore the backup on the SLAVE Server and restart mysql after this:

cat /root/mysqldump.sql | mysql -u root -p
service mysql restart

Starting replication and confirm functionality

Both servers are configured and shoud be ready for replication.

Open the MySQL console on the SLAVE Server and run the following command:

change master to master_host=’10.0.0.1‘, master_user=‘replication‘, master_password=‘password‘, master_log_file=‘mysql-bin.000001‘, master_log_pos=98;

Please note that the values for master_log_file and master_log_pos must be changed individually. The required values were previously displayed on the MASTER server as we executed the command showmasterstatus; in the MySQL command line!

Alternatively, we can also get the required values directly from the backup file, which can be done with the command:

head /root/mysqldump.sql -n 25

The output should be:

…
…
…
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
…
…

Confirm the SLAVE status with the following command:

show slave status\G

If the values master_Log_file and Read_master_log_pos are correct, we can start the replication:

start slave;

Confirm the SLAVE status again by tiping the following command:

show slave status\G

Slave_IO_Running and Slave_SQL_Running should be both Yes.

Supplement: Only replicate specific databases or ignore certain databases
Since we have only replicated the entire database structure in this tutorial so far, I would like to look at how to replicate specific databases/tables, or ignore certain databases during replication.
As an example, we will take the databases mysql and information_schema out of replication. It is not always useful to replicate them, since they are e.g. Which contains user information for the Mysql database.

On the MASTER, we open the MySQL configuration file.

nano /etc/mysql/mysql.conf.d/mysqld.cnf

an add the following lines:

binlog_ignore_db        = mysql
binlog_ignore_db        = information_schema

After that you need to restart the mysql server:

service mysql restart

Now open the the config file with the following command on the SLAVE server:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

and add the following lines:

replicate-ignore-db     = mysql
replicate-ignore-db     = information_schema

After this you have to restart mysql as well:

service mysql restart

Explination:

In short, binlog-ignore-db is a setting of the MASTER server, it does not share listet items to the SLAVE server.
replicate-ignore-db is a setting of the SLAVE server, which tells the SLAVE which databases should be ignored.

Of course, there are many other ways of database replication, e.g. Only replicate single tables. However, this would probably go beyond the framework of a tutorial.

For this I can only recommend you the MySQL documentation again:

LINK

Lastly, I recommend to periodically monitor replication, since it can be interrupted in a variety of circumstances, requiring manual intervention to restart replication.

We will add a Tutorial for automatical monitoring shortly!

In the following article, I will show you how to install a MySQL database on an Ubuntu 16.04 server and set up a replication from a master to the slave. One should however consider or note: Replication is asynchronous! The slave server lags behind the master server, depending on the load, and data loss may […]

Schade, dass dir der Artikel nicht gefallen hat.
Was sollten wir deiner Meinung nach besser machen?

Thank you for your feedback!
We will get back to you as soon as the article is finished.

Übrigens: kennst du schon unser Tutorial zum Thema Create and delete a MySQL database?

Hinterlasse einen Kommentar

0 Kommentare

×

Developer?

Get the latest gridscale developer tutorials here.
And don’t worry - we won’t spam you