How To Setup Mysql Replication On Windows

MySQL replication is a process that enables information from one MySQL database server (the master) to be copied automatically to ane or more than MySQL database servers (the slaves). It is normally used to spread read access on multiple servers for scalability, although it can also be used for other purposes such every bit for failover, or analyzing data on the slave in order non to overload the master.

As the master-slave replication is a one-manner replication (from master to slave), merely the master database is used for the write operations, while read operations may be spread on multiple slave databases. What this means is that if master-slave replication is used as the scale-out solution, you need to have at least two information sources defined, i for write operations and the second for read operations.

MySQL developers normally work on only i machine and tend to have their whole development environment on that one motorcar, with the logic that they are not dependent on a network or internet connectedness. If a master-slave replication is needed because, for instance, they need to test replication in a development environment before deploying changes elsewhere, they have to create information technology on the aforementioned automobile. While the setup of a unmarried MySQL instance is fairly simple, we need to brand some actress try to setup a second, and and then a master-slave replication.

For this step-past-pace tutorial, I've chosen Ubuntu Linux as the host operating arrangement, and the provided commands are for that operating system. If y'all desire to setup your MySQL master-slave replication on some other operating system, yous will need to make modifications for its specific commands. Yet, full general principles of setting upwards the MySQL master-slave replication on the aforementioned machine are the same for all operating systems.

Installation of the First MySQL Instance

If yous already accept one instance of MySQL database installed on your machine, you lot can skip this footstep.

The easiest fashion to install MySQL on the Ubuntu is to run the following control from a terminal prompt:

          sudo apt-become install mysql-server                  

During the installation process, yous will be prompted to ready a password for the MySQL root user.

Setting Upward mysqld_multi

In order to manage two MySQL instances on the same auto efficiently, we demand to use mysqld_multi.

First step in setting upwards mysqld_multi is the cosmos of 2 separate [mysqld] groups in the existing my.cnf file. Default location of my.cnf file on the Ubuntu is /etc/mysql/. So, open my.cnf file with your favorite text editor, and rename existing [mysqld] group to [mysqld1]. This renamed group will exist used for the configuration of the first MySQL instance and will be also configured as a master case. As in MySQL chief-slave replication each case must accept its own unique server-id, add the post-obit line in [mysqld1] group:

          server-id = i                  

Since we need a dissever [mysqld] group for the second MySQL example, copy the [mysqld1] group with all current configurations, and paste it below in the same my.cnf file. Now, rename the copied group to [mysqld2], and make the post-obit changes in the configuration for the slave:

          server-id           = 2 port                = 3307 socket              = /var/run/mysqld/mysqld_slave.sock pid-file            = /var/run/mysqld/mysqld_slave.pid datadir             = /var/lib/mysql_slave log_error           = /var/log/mysql_slave/error_slave.log relay-log           = /var/log/mysql_slave/relay-bin relay-log-index     = /var/log/mysql_slave/relay-bin.index main-info-file    = /var/log/mysql_slave/master.info relay-log-info-file = /var/log/mysql_slave/relay-log.info read_only           = 1                  

To setup the second MySQL instance as a slave, gear up server-id to ii, as information technology must be different to the master's server-id.

Since both instances will run on the same car, set port for the second case to 3307since information technology has to be different from the port used for the first case, which is 3306 by default.

In guild to enable this second instance to utilise the same MySQL binaries, we need to gear up dissimilar values for socket, pid-file, datadir and log_error.

We also need to enable relay-log in gild to utilise the second example equally a slave (parameters relay-log, relay-log-alphabetize and relay-log-info-file), too as to set master-info-file.

Finally, in order to make the slave instance read-only, parameter read_only is set to 1. You should be careful with this option since it doesn't completely prevent changes on the slave. Even when the read_only is set to 1, updates will be permitted but from users who accept the SUPER privilege. MySQL has recently introduced the new parameter super_read_only to forestall SUPER users making changes. This option is bachelor with version 5.7.viii.

Autonomously from the [mysqld1] and [mysqld2] groups, we likewise need to add a new group [mysqld_multi] to the my.cnf file:

          [mysqld_multi] mysqld     = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user       = multi_admin password   = multipass                  

One time we install the second MySQL instance, and nosotros first upwards both, we volition give appropriate privileges to the multi_admin user in order to be able to shut down MySQL instances.

Create New Folders for the 2d MySQL Case

In the previous step we prepared the configuration file for the 2d MySQL instance. In that configuration file two new folders are used. The following Linux commands should exist used in lodge to create those folders with appropriate privileges:

          mkdir -p /var/lib/mysql_slave chmod --reference /var/lib/mysql /var/lib/mysql_slave chown --reference /var/lib/mysql /var/lib/mysql_slave   mkdir -p /var/log/mysql_slave chmod --reference /var/log/mysql /var/log/mysql_slave chown --reference /var/log/mysql /var/log/mysql_slave                  

Additional Security Settings in AppArmor

In some Linux environments, AppArmor security settings are needed in order to run the second MySQL instance. At least, they are required on Ubuntu.

To properly set-up AppArmor, edit /etc/apparmor.d/usr.sbin.mysqld file with your favorite text editor, add the following lines:

          /var/lib/mysql_slave/ r, /var/lib/mysql_slave/** rwk, /var/log/mysql_slave/ r, /var/log/mysql_slave/* rw, /var/run/mysqld/mysqld_slave.pid rw, /var/run/mysqld/mysqld_slave.sock westward, /run/mysqld/mysqld_slave.pid rw, /run/mysqld/mysqld_slave.sock w,                  

After y'all save the file, reboot the machine in order for these changes to take consequence.

Installation of the Second MySQL Example

Several different approaches may be followed for the installation of the second MySQL example. The arroyo presented in this tutorial uses the same MySQL binaries as the first, with separate information files necessary for the 2d installation.

Since we have already prepared the configuration file and the necessary folders and security changes in the previous steps, the terminal installation step of the second MySQL example is the initialization of the MySQL data directory.

Execute the following command in order to initialize new MySQL data directory:

          mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave                  

Once MySQL data directory is initialized, you can showtime both MySQL instances using the mysqld_multi service:

          mysqld_multi starting time                  

Set the root password for the second MySQL instance past using the mysqladmin with the appropriate host and port. Go along in mind, if host and port are not specified, mysqladmin will connect to the first MySQL example by the default:

          mysqladmin --host=127.0.0.one --port=3307 -u root password rootpwd                  

In the example above I set the countersign to "rootpwd", but using a more than secure password is recommended.

Additional Configuration of mysqld_multi

At the end of the "Setting upwards mysqld_multi" section, I wrote that nosotros will give advisable privileges to the multi_admin user later on, so now is the time for that. Nosotros demand to requite this user appropriate privileges in both instances, and so let's starting time connect to the kickoff example:

          mysql --host=127.0.0.one --port=3306 -uroot -p                  

Once logged in, execute the post-obit ii commands:

          mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> Flush PRIVILEGES;                  

Exit from the MySQL client, and connect to the second instance:

          mysql --host=127.0.0.1 --port=3307 -uroot -p                  

Once logged in, execute the same two commands every bit above:

          mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;                  

Exit from the MySQL client.

Kickoff Both MySQL Instances Automatically on Boot

The terminal step of setting upward mysqld_multi is the installation of the automatic kick script in the init.d.

To do that, create new file named mysqld_multi in /etc/init.d, and give it advisable privileges:

          cd /etc/init.d touch mysqld_multi chmod +ten /etc/init.d/mysqld_multi                  

Open this new file with your favorite text editor, and re-create the following script:

          #!/bin/sh  ### BEGIN INIT INFO # Provides:       scriptname # Required-Beginning: $remote_fs $syslog # Required-Terminate:  $remote_fs $syslog # Default-Start:  2 3 4 5 # Default-Stop:   0 1 6 # Short-Clarification: Commencement daemon at boot fourth dimension # Description:    Enable service provided by daemon. ### Terminate INIT INFO   bindir=/usr/bin   if test -10 $bindir/mysqld_multi and then     mysqld_multi="$bindir/mysqld_multi"; else     echo "Can't execute $bindir/mysqld_multi";     exit; fi   instance "$1" in     'start' )      "$mysqld_multi" offset $two      ;;     'finish' )      "$mysqld_multi" stop $two      ;;     'report' )      "$mysqld_multi" report $ii      ;;     'restart' )      "$mysqld_multi" terminate $two      "$mysqld_multi" outset $2      ;;     *)      echo "Usage: $0 {start|cease|written report|restart}" >&ii      ;; esac                  

Add together mysqld_multi service to the default runlevels with the following command:

          update-rc.d mysqld_multi defaults                  

Reboot your machine, and check that both MySQL instances are running by using the following control:

          mysqld_multi report                  

Setup Master-slave Replication

Now, when we have two MySQL instances running on the aforementioned machine, we volition setup the kickoff example as a chief, and the second as a slave.

One function of the configuration was already performed in the chapter "Setting upwards mysqld_multi". The only remaining change in the my.cnf file is to ready binary logging on the principal. To do this, edit my.cnf file with the following changes and additions in the [mysqld1] group:

          log_bin                     = /var/log/mysql/mysql-bin.log innodb_flush_log_at_trx_commit  = 1 sync_binlog                 = 1 binlog-format               = ROW                  

Restart the master MySQL instance in lodge for these changes to take upshot:

          mysqld_multi stop 1 mysqld_multi start ane                  

In order for the slave to connect to the primary with the correct replication privileges, a new user should be created on the master. Connect to the primary instance using the MySQL client with the advisable host and port:

          mysql -uroot -p --host=127.0.0.1 --port=3306                  

Create a new user for replication:

          mysql> CREATE USER 'replication'@'%' IDENTIFIED Past 'replication'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';                  

Exit from the MySQL client.

Execute the following command in social club to create a dump of the master information:

          mysqldump -uroot -p --host=127.0.0.1 --port=3306 --all-databases --master-data=two > replicationdump.sql                  

Hither we use the option --master-information=2 in club to have a comment containing a Modify Chief statement inside the backup file. That annotate indicates the replication coordinates at the fourth dimension of the fill-in, and we will need those coordinates later for the update of primary information in the slave instance. Here is the example of that comment:

          -- -- Position to start replication or point-in-fourth dimension recovery from --  -- Modify MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=349;                  

Import the dump you created in the previous step into the slave case:

          mysql -uroot -p --host=127.0.0.i --port=3307 < replicationdump.sql                  

Finally, in social club for slave case to connect to the master case, the primary data on the slave needs to exist updated with the appropriate connectedness parameters.

Connect to the slave instance using the MySQL client with the advisable host and port:

          mysql -uroot -p --host=127.0.0.1 --port=3307                  

Execute the following command in guild to update the master information (have the replication coordinates from the dump file replicationdump.sql, every bit explained above):

          mysql> Change Master TO   -> MASTER_HOST='127.0.0.1',   -> MASTER_USER='replication',   -> MASTER_PASSWORD='replication',   -> MASTER_LOG_FILE='mysql-bin.000001',   -> MASTER_LOG_POS=349;                  

Execute the post-obit command in order to commencement the slave:

          mysql> START SLAVE;                  

Execute the post-obit control in gild to verify the replication is up and running:

          mysql> SHOW SLAVE STATUS \G                  

Congratulations. Your MySQL primary-slave replication on the same machine is now successfully set upward.

Wrap Up

Having a chief-slave replication configured in your development environs is useful if yous need it for a scale-out solution in the product environment. This mode, you lot will also have dissever data sources configured for write and read operations and then you lot can exam locally that everything works as expected before further deployment.

Additionally, you may want to have several slave instances configured on the same auto to test the load balancer that distributes the read operations to several slaves. In that example, you may apply this same transmission to setup other slave instances by repeating all the same steps.

Source: https://www.toptal.com/mysql/mysql-master-slave-replication-tutorial

Posted by: overcashsheastes.blogspot.com

Share this post