This post assumes that you have a user with sudo privileges and have not installed MySQL. If you do have MySQL installed, then you can skip the installation step and move to the configuration of master/slave database:
MySQL installation on the MASTER server
Install MySQL server and MySQL client:# yum install mysql-community-server
Connect to MySQL shell in order to create the new user “dbuser” and the database “testdb”:
# mysql -u root -p
mysql> create database testdb;
mysql> grant usage on *.* to dbuser@localhost identified by ‘password’;
mysql> grant all privileges on testdb.* to dbuser@localhost;
mysql> exit;
Edit the MySQL configuration file to allow remote connections to this server:
# vi /etc/mysql/my.cnf
Find the line “bind-address = 127.0.0.1” and comment it if it’s not already done:
#bind-address = 127.0.0.1
MySQL installation on SLAVE server
For the moment, just install MySQL server and MySQL client on the SLAVE server:# yum install mysql-community-server
Configure MySQL replication on the MASTER Database server
Edit the MySQL configuration file to enable master database replication:
# vi /etc/mysql/my.cnf
Find the line “server-id” and set it to 1:
server-id = 1
Move on to the "log _ bin" line.
This is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log.
For this step we simply need to uncomment the line that refers to "log _ bin" and add the "expire _ logs _ days" to avoid a huge amount of log files filling up the disk:
log-bin = mysql-bin.log
expire_logs_days = 7
Finally, we need to designate the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need:
binlog_do_db = testdb
After making all of the changes, go ahead and save and exit out of the configuration file, then restart MySQL service.
# service mysql restart
The next steps will take place in the MySQL shell, itself. We will grant privileges to a new “slave_user” who will be used for the replication (please choose a password):
# mysql -u root -p
mysql> grant REPLICATION SLAVE on *.* to 'slave_user'@'%' IDENTIFIED BY 'password';
mysql> flush privileges;
To continue, please open a new SSH connection to the master server, in a new shell window.
In your current SSH window, switch to testdb database and lock the database to prevent any changes:
mysql> use testdb;
mysql> flush tables with read lock;
Then type:
mysql> show master status;
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 403 | tesdb | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
You will see a tab with some information. The important one is "Position". This is the position from which the slave database will start replicating. We will use this position number later.
If you make any new changes in the same SSH window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.
Proceeding with the database still locked, export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).
# mysqldump -u root -p --opt testdb > testdb.sql
Now, returning to your original SSH window, unlock the databases (making them writeable again). Finish up by exiting the shell:
mysql> unlock tables;
mysql> exit;
Now you are all done with the configuration on the master side.
Configure MySQL replication on the SLAVE Database server
Log into your slave server, open up the MySQL shell and create the new database and user (same as before):
# mysql -u root -p
mysql> create database testdb;
mysql> grant usage on *.* to dbuser@localhost identified by ‘password’;
mysql> grant all privileges on testdb.* to dbuser@localhost;
mysql> exit;
Import the database that you previously exported from the master server:
# mysql -h localhost -u root -p testdb < testdb.sql
Now we need to configure the slave configuration in the same way as we did for the master:
vi /etc/mysql/my.cnf
Find the line “bind-address = 127.0.0.1” and comment it if it’s not already done:
#bind-address = 127.0.0.1
Find the line “server-id” and set it to 2:
server-id = 2
Following that, make sure that you have the following 4 criteria appropriately filled out: (You will need to add the last 2 lines which are not there by default.):
log-bin = mysql-bin.log
binlog_do_db = alfresco
relay-log = mysql-relay-bin.log
relay_log_space_limit=2G
After you make all of the changes, go ahead and save and exit out of the configuration file, then restart MySQL service.
# service mysql restart
The next step is to enable the replication from within the MySQL shell.
Open up the MySQL shell once again and type in the following command, replacing the values to match your information which is in the previous tab (on the master):
mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.62',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 403;
This command accomplishes several things at the same time:
- It designates the current server as the slave of our master
- It provides the server with the correct login credentials
- It lets the slave server know where to start replicating from
With that, you have configured the master and slave server. Activate the slave server:
mysql> start slave;
You can see the details of the slave replication by typing in the following command.
The \G rearranges the text to make it more readable.
You should have something like this:
Thanks!!
RANDYDBA BLOG { DATABASE - TIPS & TECHNIQUES }
serving to discover
Comments
Post a Comment