MySQL replication is a core process for maintaining multiple copies of data – and replication is a very important aspect in database administration.
This setup provides redundancy and fault tolerance such that in the event of a failure in the Master node, the databases can still be recovered in the Slave node.
Step
1. Setup the config file
We need to locate MySQL configuration file, containing
[mysqld] section.
Next we need to set up some things related to master
and slave configurations. Let me paste the entire
[root@oracleasm1 mysql]# mysql --help | grep cnf order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf [root@oracleasm1 mysql]# cat /etc/my.cnf [mysqld] server-id = 1 log_bin = /var/lib/mysql/mysql-bin.log log_bin_index =/var/lib/mysql/mysql-bin.log.index relay_log_index = /var/lib/mysql/mysql-relay-bin.index report-host=master-is-slave-host relay-log=mysql-bin replicate-same-server-id=1 binlog-do-db=master replicate-rewrite-db=master->slave replicate-do-db=slave log_bin = /var/lib/mysql/mysql-bin.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/lib/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
In the sample above we are going to replicate Master
database to database named slave. We have defined these databases for
replication, now we need to restart our MySQL server for the changes to take
effect.
[root@oracleasm1 mysql]# service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] [root@oracleasm1 mysql]# mysql -u root -p mysql> show databases ; +--------------------------+ | Database | +--------------------------+ | information_schema | | master | | mysql | | nexaprod | | replication_db | | slave | | tutorial_database | +--------------------------+
Now we need to check whether master has started. Log in to mysql as root and check the status of master as follows :
mysql> SHOW MASTER STATUS\G
*************************** 1. row *************************** File: mysql-bin.000021 Position: 1235 Binlog_Do_DB: master Binlog_Ignore_DB: 1 row in set (0.00 sec)
Make sure to record File and Position values, we will need them on the next step. At this point, I have both databases (master/slave) are ready.
Step 2 : Create a new replication user on master node and grant the necessary privileges.
mysql> CREATE USER 'replication_user'@'192.168.56.102' IDENTIFIED BY 'xyz'; Query OK, 0 rows affected (0.03 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'192.168.56.102'; Query OK, 0 rows affected (0.00 sec)
Step 3. Assign the replica server
Stop the slave and execute below command to assign replica database user (same user which was granted replication slave) to our master server so as to allow the Slave server to replicate the Master server
Note : If you won’t do that stop the slave DB, you will get an error message as below telling you it’s absolutely necessary to do.
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop slave ; Query OK, 0 rows affected (0.01 sec) mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.102' , MASTER_USER ='replication_user' , MASTER_PASSWORD ='xyz',
MASTER_LOG_FILE = 'mysql-bin.0000021', MASTER_LOG_POS = 1235;
Query OK, 0 rows affected (0.12 sec)
Where,
- MASTER_HOST: the IP or hostname of the master server, in this example blue or'192.168.56.102'
- MASTER_USER: this is the user we granted the REPLICATION SLAVE privilege to in Step 2, in this example, “replication_user”
- MASTER_PASSWORD: this is the password we assigned to ”replication_user” in Step 2
- MASTER_LOG_FILE: is an empty string (wouldn’t be empty if there were existing writes to be picked up from the master)
- MASTER_LOG_POS: is 1235 (would likely be different if there were existing writes to be picked up from the master)
You will observe that we’ve used the mysql-bin. 0000021 value and position ID 1235 earlier displayed after creating the slave replication user.
Step
4. Start the replica DB and validate the configuration
mysql> start slave ; Query OK, 0 rows affected (0.02 sec) -- Log 200916 15:47:11 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='192.168.56.102', master_port='3306', master_log_file='mysql-bin.0000021', master_log_pos='740'. New state master_host='192.168.56.102', master_port='3306', master_log_file='mysql-bin.000021', master_log_pos='1235'. 200916 15:47:16 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000021' a t position 1235, relay log './mysql-bin.000022' position: 4 200916 15:47:16 [Note] Slave I/O thread: connected to master 'replication_user@192.168.56.102:3306',replication started in log 'mysql-bin.000021' at position 1235
Basic
Checks :
If everything is fine, you will get something like below:
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.102 Master_User: replication_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000021 Read_Master_Log_Pos: 4481 Relay_Log_File: mysql-bin.000023 Relay_Log_Pos: 1691 Relay_Master_Log_File: mysql-bin.000021 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: slave Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 4481 Relay_Log_Space: 1840 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.01 sec) mysql>
No comments:
Post a Comment