![]() MASTER_LOG_FILE – file MySQL gave back in step #1 when you ran MASTER_PASSWORD – password of slave user we crated in step #2. MASTER_USER – slave user we created in step #2. MASTER_HOST – IP address or hostname of the master (54.24.32.12). While you are logged into MySQL, run the following commands to initiate replication process. Load data from master database to populate slave database mysql> LOAD DATA FROM MASTER īonus Read : Top Database Blogs to Follow Run the following commands to create an empty database on slave. Restart MySQL Server and log into MySQL $ sudo service mysql restart Last line specifies the database to be replicated. Next couple of lines specify locations of relay log and replication log files. In the first line, we assign server ID of 2 to slave server. Relay-log = /var/log/mysql/mysql-relay-bin.log Open terminal on slave database’s server and run the following command $ sudo vi /etc/mysql/my.cnfĪdd the following lines under to look like This remote user needs to connect to master database for replication to happen.īonus Read : How to Change Collation of All Tables in MySQL Please add an inbound firewall rule for port 3306 and allow 45.12.21.23. Mysql> GRANT REPLICATION SLAVE ON *.* TO 45.12.21.23 Please replace 45.12.21.23 below with your slave server’s IP, and replace $password with a suitable password as per your requirement. $ sudo mysql -u root -pĪfter logging into MySQL, run the following commands to create a remote user slave_user, and grant it replication permissions for all databases. ![]() Please note the above details, we will need them later in Step #3.īonus Read : How to Enable SSL/TLS in MySQL | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Log into MySQL as root user $ sudo mysql -u root -pĪnd run the following command mysql> SHOW MASTER STATUS Restart MySQL Server to apply changes $ sudo service mysql restart Log-bin specifies the log file location that will be populated by MySQL with details of replication.īinlog-do-db indicates the name of database that needs to be replicated. It needs to be a positive number from 1 to 2^32. The first line server-id specifies a unique ID number for master. server-id=1 log-bin = /var/log/mysql/mysql-bin.log So we uncomment the following two lines to allow remote connections, by adding # at their beginning, as shown below #skip-networkingĪdd or uncomment the following lines in block to look like the following. We need to allow remote connection from slave to master for replication. Open terminal on master database’s server and run the following command $ sudo vi /etc/mysql/my.cnfīy default, remote connections are disabled in MySQL. ![]() Else you can install MySQL with following command $ sudo apt-get install mysql-server mysql-clientīonus Read : Top MySQL Workbench Alternatives We have assumed that you have MySQL installed on both these servers, and you have root privileges for both of them. ![]() We will replicate database named exampledb from master to slave. For our setup, we will need a master database (IP – 54.24.32.12) and a slave database (IP – 45.12.21.23). Here are the steps to replicate MySQL database. You can use these steps to replicate MySQL database in Ubuntu, Debian, CentOS, Fedora, Red hat and other types of Linux. In this article, we will look Master-Slave replication in MySQL and learn how to replicate MySQL database in Linux. MariaDB also supports multi-master replication. MySQL supports different kind of replication such as master-slave, master-master and group replication. MySQL Replication allows you to easily copy database from one server to another.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |