Cara menggunakan mysql replication gap
MySQL replication is a process that enables data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves). It is usually used to spread read access on multiple servers for scalability, although it can also be used for other purposes such as for failover, or analyzing data on the slave in order not to overload the master. Show
As the master-slave replication is a one-way replication (from master to slave), only 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 data sources defined, one for write operations and the second for read operations. MySQL developers usually work on only one machine and tend to have their whole development environment on that one machine, with the logic that they are not dependent on a network or internet connection. If a master-slave replication is needed because, for example, they need to test replication in a development environment before deploying changes elsewhere, they have to create it on the same machine. While the setup of a single MySQL instance is fairly simple, we need to make some extra effort to setup a second, and then a master-slave replication. For this step-by-step tutorial, I’ve chosen Ubuntu Linux as the host operating system, and the provided commands are for that operating system. If you want to setup your MySQL master-slave replication on some other operating system, you will need to make modifications for its specific commands. However, general principles of setting up the MySQL master-slave replication on the same machine are the same for all operating systems. Installation of the First MySQL InstanceIf you already have one instance of MySQL database installed on your machine, you can skip this step. The easiest way to install MySQL on the Ubuntu is to run the following command from a terminal prompt:
During the installation process, you will be prompted to set a password for the MySQL Setting Up mysqld_multiIn order to manage two MySQL instances on the same machine efficiently, we need to use First step in setting up
Since we need a separate
To setup the second MySQL instance as a slave, set Since both instances will run on the same machine, set In order to enable this second instance to use the same MySQL binaries, we need to set different values for We also need to enable Finally, in order to make the slave instance read-only, parameter Apart from the
Once we install the second MySQL instance, and we start up both, we will give appropriate privileges to the Create New Folders for the Second MySQL InstanceIn the previous step we prepared the configuration file for the second MySQL instance. In that configuration file two new folders are used. The following Linux commands should be used in order to create those folders with appropriate privileges:
Additional Security Settings in AppArmorIn 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
After you save the file, reboot the machine in order for these changes to take effect. Installation of the Second MySQL InstanceSeveral different approaches may be followed for the installation of the second MySQL instance. The approach presented in this tutorial uses the same MySQL binaries as the first, with separate data files necessary for the second installation. Since we have already prepared the configuration file and the necessary folders and security changes in the previous steps, the final installation step of the second MySQL instance is the initialization of the MySQL data directory. Execute the following command in order to initialize new MySQL data directory:
Once MySQL data directory is initialized, you can start both MySQL instances using the
Set the
In the example above I set the password to “rootpwd”, but using a more secure password is recommended. Additional Configuration of mysqld_multiAt the end of the “Setting up mysqld_multi” section, I wrote that we will give appropriate privileges to the
Once logged in, execute the following two commands:
Exit from the MySQL client, and connect to the second instance:
Once logged in, execute the same two commands as above:
Exit from the MySQL client. Start Both MySQL Instances Automatically on BootThe final step of setting up
To do that, create new file named
Open this new file with your favorite text editor, and copy the following script:
Add
Reboot your machine, and check that both MySQL instances are running by using the following command:
Setup Master-slave ReplicationNow, when we have two MySQL instances running on the same machine, we will setup the first instance as a master, and the second as a slave. One part of the configuration was already performed in the chapter “Setting up mysqld_multi”. The only remaining change in the
Restart the master MySQL instance in order for these changes to take effect:
In order for the slave to connect to the master with the correct replication privileges, a new user should be created on the master. Connect to the master instance using the MySQL client with the appropriate host and port:
Create a new user for replication:
Exit from the MySQL client. Execute the following command in order to create a dump of the master data:
Here we use the option
Import the dump you created in the previous step into the slave instance:
Finally, in order for slave instance to connect to the master instance, the master information on the slave needs to be updated with the appropriate connection parameters. Connect to the slave instance using the MySQL client with the appropriate host and port:
Execute the following command in order to update the master information (take the replication coordinates from the dump file
Execute the following command in order to start the slave:
Execute the following command in order to verify the replication is up and running:
Congratulations. Your MySQL master-slave replication on the same machine is now successfully set up. Wrap UpHaving a master-slave replication configured in your development environment is useful if you need it for a scale-out solution in the production environment. This way, you will also have separate data sources configured for write and read operations so you can test locally that everything works as expected before further deployment. Additionally, you may want to have several slave instances configured on the same machine to test the load balancer that distributes the read operations to several slaves. In that case, you may use this same manual to setup other slave instances by repeating all the same steps. Further Reading on the Toptal Engineering Blog:
|