MySQL Master-Slave Database Replication:
=============================================
Master DB IP: 192.168.0.251
Slave DB IP: 192.168.0.252
On Master Server:
=================
1. CentOS with yum updated
2. MySQL installed with service up
3. Static IP
4. For safer side we can take all mysql database backup
5. First Take master-1 db backup and restore it on second master server
=========================================
1. Configure On Master Server:
Add Below Line in /etc/my.cnf
server_id= 2154 # Enter Unique Server ID, Its mandatory to add this line
log-bin=mysql-bin
log_bin_index=log-bin-index
sync_binlog=1
expire_logs_days=10
log_bin_trust_function_creators=1
binlog_format=MIXED
# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id= 2154 # Enter Unique Server ID
log-bin=mysql-bin
log_bin_index=log-bin-index
sync_binlog=1
expire_logs_days=10
log_bin_trust_function_creators=1
binlog_format=MIXED
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
:wq
2. Verify Once Again:
# cat /etc/my.cnf
3. Login to MySQL Server Prompt and Create Replication User:
# mysql -u root -p
mysql> Create user replicauser@'%' identified by 'mypassword';
mysql> grant replication slave on *.* to 'replicauser'@'%';
mysql> flush privileges;
mysql> select version();
mysql> show variables like 'server%';
mysql> show databases;
mysql> quit
4. Now, Restart MySQL Service
# service mysqld stop
# service mysqld start
5. Login to MySQL & Check Master Databaser Server Status:
# mysql -u root -p
mysql> show master status;
mysql> SELECT table_schema "Database Name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES WHERE table_schema = 'db_name'GROUP BY table_schema;
mysql> SELECT table_schema "Database Name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES -- WHERE table_schema = 'db_name'GROUP BY table_schema;
mysql> SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema;
mysql> \q
6. Login MySQL database and check database size:
# mysql -u root -p
mysql> SELECT table_schema "Database Name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES WHERE table_schema like '%test_%'
GROUP BY table_schema;
[Note: '%test_%' :- test is database starting name ]
7. Finaly Check Master Status:
mysql> show master status;
=========================================================================================================================================================================
=========================================================================================================================================================================
Now Configure Slave Server:
===========================
1. CentOS 6.5 with yum updated
2. MySQL installed with service up
3. Static IP
========================================================
1. Configuration on Slave Server:
# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id=9453 # Enter Unique Server ID, Its mandatory to add this line
relay_log=mysql-relay
relay_log_index=relay-log-index
skip-slave-start
# replicate only below DBs
replicate_do_db = test_artidfd
replicate_do_db = test_blog_dfd
replicate_do_db = test_dfd_df
replicate_do_db = test_dgd_df
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
:wq
===============================
2. Check Database Service Status and stop/start it:
# service mysqld status
# service mysqld stop/start
3. Login mysql and check status:
mysql> select user, host from mysql.user;
mysql> show variables like '%uuid%';
mysql> select version();
mysql> show variables like 'server%';
mysql> show databases;
mysql> show slave status \G
mysql> stop slave;
mysql> CHANGE MASTER TO
> MASTER_HOST='192.168.0.251', # master server ip
> MASTER_USER='replicauser', # master server replicatation user
> MASTER_PASSWORD='mypassword', # master server replicatation user password
> MASTER_PORT=3306, # mysql port number
> MASTER_LOG_FILE='mysql-bin.000002', # fire command on master server # show master status; & enter output here
> MASTER_LOG_POS=103111, # fire command on master server # show master status; & enter output here
> MASTER_CONNECT_RETRY=10;
OR Direct fire commands:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.251', MASTER_USER='replicauser', MASTER_PASSWORD='mypassword', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=103111, MASTER_CONNECT_RETRY=10;
mysql> start slave;
mysql> show slave status \G
mysql> show databases;
mysql> \q
4. Now create one folder to take database backup from master server:
# mkdir db_dump
# cd db_dump/
5. Write one script as below:
# vi backup_dbs.sh
mysqldump -h '192.168.0.251' -u dbusername -p'dbpassword' test_article_dfd > /home/sysuser/db_dump/test_cf_acx.sql ;
mysqldump -h '192.168.0.251' -u dbusername -p'dbpassword' test_blog_df > /home/sysuser/db_dump/test_blog_xvd.sql ;
:wq
For Example:
mysqldump -h 'master-server-ip' -u root -p'root-passwd' db-name > /slave-server-path/db-name.sql ;
Note: its depend on database name.
# sh backup_dbs.sh
6. Once Backup Done, Restore it:
7. Write a restore database script as below:
# vi restore.sh
mysql -u dbusername -p'dbpassword' test_article_df < /home/sysuser/db_dump/test_article_df.sql ;
mysql -u dbusername -p'dbpassword' test_blog_df < /home/sysuser/db_dump/test_blog_df.sql ;
:wq
8. Execute Script:
# sh restore.sh
9. Login to mysql and check status:
mysql> start slave;
mysql> show slave status \G
mysql> \q
==============================================================================================================================
Testing:
Now, Finally Once MySQL Databse Replication Completed then Just test with below commands, is it working or now:
On Master Server:
mysql> show databases;
mysql> use test_article_df;
mysql> show tables;
mysql> create table suren_test(id int(10));
mysql> show tables;
On Slave Server Check replication done or not:
# mysql -u root -p
mysql> use test_article_management;
mysql> show tables;
If created table showing on slave server that means your replication is successfully configured.
If we want to add newly created database on master server in replication then:
1. First create database on master server
2. on slave server:
3. stop slave;
4. edit my.cnf file and add replicatation database name as newly created
5. stop/start mysql service
6. now start slave
7. check now status of slave
8. create database on slave same as on master.
9. if existing database having data then take backup and restore it on slave
Note:
1. When we created new database on master server then we have to add it manually on slave server and restart mysql service only on slave server
2. For temporary we can test with existing database to create tables only as given above. and check on slave server without restarting mysql service on master database
=======================================================================================================================================================================
Note: we can crate additional database user for testing: on master
Create MySQL user with all database full privilages for any host:
create user dbusername@'%' identified by 'dbpassword';
grant all on *.* to dbusername@'%' with grant option;
flush privileges;
=============================================================
To check mysql replication:
On master database:
# show databases;
# show master status;
On Slave databases:
# SHOW GLOBAL STATUS like 'slave_running';
Output:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slave_running | ON |
+---------------+-------+
Skip error and solution:
STOP SLAVE;
set global sql_slave_skip_counter=1;
START SLAVE;
show slave status \G
=============================================
Master DB IP: 192.168.0.251
Slave DB IP: 192.168.0.252
On Master Server:
=================
1. CentOS with yum updated
2. MySQL installed with service up
3. Static IP
4. For safer side we can take all mysql database backup
5. First Take master-1 db backup and restore it on second master server
=========================================
1. Configure On Master Server:
Add Below Line in /etc/my.cnf
server_id= 2154 # Enter Unique Server ID, Its mandatory to add this line
log-bin=mysql-bin
log_bin_index=log-bin-index
sync_binlog=1
expire_logs_days=10
log_bin_trust_function_creators=1
binlog_format=MIXED
# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id= 2154 # Enter Unique Server ID
log-bin=mysql-bin
log_bin_index=log-bin-index
sync_binlog=1
expire_logs_days=10
log_bin_trust_function_creators=1
binlog_format=MIXED
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
:wq
2. Verify Once Again:
# cat /etc/my.cnf
3. Login to MySQL Server Prompt and Create Replication User:
# mysql -u root -p
mysql> Create user replicauser@'%' identified by 'mypassword';
mysql> grant replication slave on *.* to 'replicauser'@'%';
mysql> flush privileges;
mysql> select version();
mysql> show variables like 'server%';
mysql> show databases;
mysql> quit
4. Now, Restart MySQL Service
# service mysqld stop
# service mysqld start
5. Login to MySQL & Check Master Databaser Server Status:
# mysql -u root -p
mysql> show master status;
mysql> SELECT table_schema "Database Name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES WHERE table_schema = 'db_name'GROUP BY table_schema;
mysql> SELECT table_schema "Database Name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES -- WHERE table_schema = 'db_name'GROUP BY table_schema;
mysql> SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema;
mysql> \q
6. Login MySQL database and check database size:
# mysql -u root -p
mysql> SELECT table_schema "Database Name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES WHERE table_schema like '%test_%'
GROUP BY table_schema;
[Note: '%test_%' :- test is database starting name ]
7. Finaly Check Master Status:
mysql> show master status;
=========================================================================================================================================================================
=========================================================================================================================================================================
Now Configure Slave Server:
===========================
1. CentOS 6.5 with yum updated
2. MySQL installed with service up
3. Static IP
========================================================
1. Configuration on Slave Server:
# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id=9453 # Enter Unique Server ID, Its mandatory to add this line
relay_log=mysql-relay
relay_log_index=relay-log-index
skip-slave-start
# replicate only below DBs
replicate_do_db = test_artidfd
replicate_do_db = test_blog_dfd
replicate_do_db = test_dfd_df
replicate_do_db = test_dgd_df
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
:wq
===============================
2. Check Database Service Status and stop/start it:
# service mysqld status
# service mysqld stop/start
3. Login mysql and check status:
mysql> select user, host from mysql.user;
mysql> show variables like '%uuid%';
mysql> select version();
mysql> show variables like 'server%';
mysql> show databases;
mysql> show slave status \G
mysql> stop slave;
mysql> CHANGE MASTER TO
> MASTER_HOST='192.168.0.251', # master server ip
> MASTER_USER='replicauser', # master server replicatation user
> MASTER_PASSWORD='mypassword', # master server replicatation user password
> MASTER_PORT=3306, # mysql port number
> MASTER_LOG_FILE='mysql-bin.000002', # fire command on master server # show master status; & enter output here
> MASTER_LOG_POS=103111, # fire command on master server # show master status; & enter output here
> MASTER_CONNECT_RETRY=10;
OR Direct fire commands:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.251', MASTER_USER='replicauser', MASTER_PASSWORD='mypassword', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=103111, MASTER_CONNECT_RETRY=10;
mysql> start slave;
mysql> show slave status \G
mysql> show databases;
mysql> \q
4. Now create one folder to take database backup from master server:
# mkdir db_dump
# cd db_dump/
5. Write one script as below:
# vi backup_dbs.sh
mysqldump -h '192.168.0.251' -u dbusername -p'dbpassword' test_article_dfd > /home/sysuser/db_dump/test_cf_acx.sql ;
mysqldump -h '192.168.0.251' -u dbusername -p'dbpassword' test_blog_df > /home/sysuser/db_dump/test_blog_xvd.sql ;
:wq
For Example:
mysqldump -h 'master-server-ip' -u root -p'root-passwd' db-name > /slave-server-path/db-name.sql ;
Note: its depend on database name.
# sh backup_dbs.sh
6. Once Backup Done, Restore it:
7. Write a restore database script as below:
# vi restore.sh
mysql -u dbusername -p'dbpassword' test_article_df < /home/sysuser/db_dump/test_article_df.sql ;
mysql -u dbusername -p'dbpassword' test_blog_df < /home/sysuser/db_dump/test_blog_df.sql ;
:wq
8. Execute Script:
# sh restore.sh
9. Login to mysql and check status:
mysql> start slave;
mysql> show slave status \G
mysql> \q
==============================================================================================================================
Testing:
Now, Finally Once MySQL Databse Replication Completed then Just test with below commands, is it working or now:
On Master Server:
mysql> show databases;
mysql> use test_article_df;
mysql> show tables;
mysql> create table suren_test(id int(10));
mysql> show tables;
On Slave Server Check replication done or not:
# mysql -u root -p
mysql> use test_article_management;
mysql> show tables;
If created table showing on slave server that means your replication is successfully configured.
If we want to add newly created database on master server in replication then:
1. First create database on master server
2. on slave server:
3. stop slave;
4. edit my.cnf file and add replicatation database name as newly created
5. stop/start mysql service
6. now start slave
7. check now status of slave
8. create database on slave same as on master.
9. if existing database having data then take backup and restore it on slave
Note:
1. When we created new database on master server then we have to add it manually on slave server and restart mysql service only on slave server
2. For temporary we can test with existing database to create tables only as given above. and check on slave server without restarting mysql service on master database
=======================================================================================================================================================================
Note: we can crate additional database user for testing: on master
Create MySQL user with all database full privilages for any host:
create user dbusername@'%' identified by 'dbpassword';
grant all on *.* to dbusername@'%' with grant option;
flush privileges;
=============================================================
To check mysql replication:
On master database:
# show databases;
# show master status;
On Slave databases:
# SHOW GLOBAL STATUS like 'slave_running';
Output:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slave_running | ON |
+---------------+-------+
Skip error and solution:
STOP SLAVE;
set global sql_slave_skip_counter=1;
START SLAVE;
show slave status \G
No comments:
Post a Comment