MySQL Master<\/h4>\n

You will call MySQL instance ” Master<\/strong> ” in replication infrastructure where your data is stored and one to be replicated, The following are configuration changes recommended to setup MySQL Master my.cnf:<\/p>\n

\n
• Configure MySQL Master instance with server_id<\/li>\n
• Configure MySQL Master with binary logging<\/li>\n<\/ul>\n

  [mysqld]\r\nlog-bin=mysql-bin\r\nserver-id=101<\/pre>\n
  MySQL Slave<\/h4>\n
  In MySQL Slave, The settings will be same as the MySQL master, except the server-id on slave will be unique (in this example, we have made MySQL slave instance server-id = 102\u00a0<\/strong>)<\/p>\n
  [mysqld]\r\nserver-id=102<\/pre>\n
  Percona XtraBackup<\/h4>\n
  Percona XtraBackup is a open source \/ free MySQL Hot-backup \u00a0tool from Percona to complete high-speed online backup of your MySQL database<\/p>\n
  Step 1: Complete full database backup with Percona XtraBackup and prepare the same<\/h3>\n
  Issue the following command on the shell of the Master:<\/p>\n
  MinervaDBMaster$ xtrabackup --backup --user=TheDBUser --password=TheDBUserPa55wD --target-dir=\/dir11\/backupdir\/fullbackup<\/pre>\n
  If full-backup completed successfully, You should be seeing:<\/p>\n
  xtrabackup: completed OK!<\/pre>\n
  So you have now made a copy of your MySQL data dir to the directory \/dir11\/backupdir\/fullbackup<\/span><\/em><\/p>\n
  The consistency of snapshot ( backup ) completed above is very important to address data reliability concerns, So in the \u00a0next command \u00a0below we will prepare the \u00a0backup completed in previous step:<\/p>\n
  MinervaDBMaster$ xtrabackup --user=TheDBUser --password=TheDBUserPa55wD --prepare --target-dir=\/dir11\/backupdir\/fullbackup<\/pre>\n
  The previous step (prepare backup) applies transaction logs to the data files and makes those data files ready to be restored \u00a0on another MySQL Server ( in our case it will be slave server)<\/p>\n
  Note – Percona XtraBackup knows default location of your my.cnf to locate data files. But, if you are retaining my.cnf on non-standard place, please\u00a0use the flag –defaults-file=\/nonstandard-location\/of\/my.cnf\u00a0<\/i><\/span><\/p>\n
  Step 2: Restore the backup to Slave Server<\/h3>\n
  You can directly rsync or scp to copy data from Master to Slave’s data directory but please stop mysqld in slave:<\/p>\n
  MinervaDBMaster$ rsync -avpP -e ssh \/dir11\/backupdir\/fullbackup MinervaDBSlave:\/home\/Backup-From-Master\/<\/pre>\n
  Once data is successfully copied, Please backup the previously installed MySQL datadir ( Note – Before copying the data from datadir or moving snapshot to its datadir\u00a0please shutdown mysqld<\/span>\u00a0<\/i>)<\/p>\n
  MinervaDBSlave$ mv \/var\/lib\/mysql\/datadir \/Bakup\/db-bkp\/mysql\/mysql-datadir_bakup<\/pre>\n
  Please move snapshot from the Master to datadir of Slave:<\/p>\n
  MinervaDBSlave$ xtrabackup --move-back --target-dir=\/home\/Backup-From-Master\/<\/pre>\n
  Once successfully completed the copying of data, Please make sure MySQL has permissions to access them<\/p>\n
  MinervaDBSlave$ chown -R mysql:mysql \/var\/lib\/mysql\/<\/pre>\n
  Step 3: Configuring MySQL Replication<\/h3>\n
  Grant login privileges for \u00a0slave to connect to Master:<\/p>\n
  MinervaDBMaster|mysql> GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'$slaveip' IDENTIFIED BY 'SlavePa55wd';<\/pre>\n
  Confirm you can connect to MySQL Master \u00a0from MySQL Slave instance:<\/p>\n
  MinervaDBSlave$ mysql --host=MinervaDBMaster --user=repl --password=SlavePa55wd<\/pre>\n
  Step 4: Configuring MySQL Slaver Server<\/h3>\n
  Copy my.cnf\u00a0<\/em>from MySQL Master to Slave and please don’t forget to change the server-id of the slave ( in this post we have configured slave with server_id=102 )<\/p>\n
  Step 5: Start MySQL Replication<\/h3>\n
  You can view MySQL master log file<\/strong> and position<\/strong> from\u00a0xtrabackup_binlog_pos_innodb<\/span><\/em>\u00a0<\/span>of the slave:<\/p>\n
  root@MinervaDBSlave:\/var\/lib\/mysql# cat xtrabackup_binlog_pos_innodb\r\nmysql-bin.000409        336216351\r\nroot@MinervaDBSlave:\/var\/lib\/mysql#<\/pre>\n
  Configuring slave to start replication:<\/p>\n
  TheSlave|mysql> CHANGE MASTER TO\r\n                MASTER_HOST='$masterip',\r\n                MASTER_USER='repl',\r\n                MASTER_PASSWORD='SlavePa55wd',\r\n                MASTER_LOG_FILE='mysql-bin.000409',\r\n                MASTER_LOG_POS=336216351;<\/pre>\n
  Start MySQL slave:<\/p>\n
  MinervaDBSlave|mysql> START SLAVE;<\/pre>\n
  Monitoring MySQL Replication from Slave:<\/h4>\n
  mysql> show slave status\\G\r\n*************************** 1. row ***************************\r\n               Slave_IO_State: Waiting for master to send event\r\n                  Master_Host: 10.168.0.134\r\n                  Master_User: repl\r\n                  Master_Port: 3306\r\n                Connect_Retry: 60\r\n              Master_Log_File: mysql-bin.000465\r\n          Read_Master_Log_Pos: 671936951\r\n               Relay_Log_File: flip-db3-relay-bin.000169\r\n                Relay_Log_Pos: 671825092\r\n        Relay_Master_Log_File: mysql-bin.000465\r\n             Slave_IO_Running: Yes\r\n            Slave_SQL_Running: Yes\r\n              Replicate_Do_DB: \r\n          Replicate_Ignore_DB: \r\n           Replicate_Do_Table: \r\n       Replicate_Ignore_Table: \r\n      Replicate_Wild_Do_Table: \r\n  Replicate_Wild_Ignore_Table: \r\n                   Last_Errno: 0\r\n                   Last_Error: \r\n                 Skip_Counter: 0\r\n          Exec_Master_Log_Pos: 671824879\r\n              Relay_Log_Space: 671937421\r\n              Until_Condition: None\r\n               Until_Log_File: \r\n                Until_Log_Pos: 0\r\n           Master_SSL_Allowed: No\r\n           Master_SSL_CA_File: \r\n           Master_SSL_CA_Path: \r\n              Master_SSL_Cert: \r\n            Master_SSL_Cipher: \r\n               Master_SSL_Key: \r\n        Seconds_Behind_Master: 0\r\nMaster_SSL_Verify_Server_Cert: No\r\n                Last_IO_Errno: 0\r\n                Last_IO_Error: \r\n               Last_SQL_Errno: 0\r\n               Last_SQL_Error: \r\n  Replicate_Ignore_Server_Ids: \r\n             Master_Server_Id: 100\r\n                  Master_UUID: d3575a73-1f76-11ea-a643-00163e016ecb\r\n             Master_Info_File: \/var\/lib\/mysql\/master.info\r\n                    SQL_Delay: 0\r\n          SQL_Remaining_Delay: NULL\r\n      Slave_SQL_Running_State: Reading event from the relay log\r\n           Master_Retry_Count: 86400\r\n                  Master_Bind: \r\n      Last_IO_Error_Timestamp: \r\n     Last_SQL_Error_Timestamp: \r\n               Master_SSL_Crl: \r\n           Master_SSL_Crlpath: \r\n           Retrieved_Gtid_Set: \r\n            Executed_Gtid_Set: \r\n                Auto_Position: 0\r\n         Replicate_Rewrite_DB: \r\n                 Channel_Name: \r\n           Master_TLS_Version: \r\n1 row in set (0.00 sec)\r\n<\/pre>\n
  Interpreting the results of ” show slave status\\G\u00a0<\/em>”<\/p>\n
  \n
  Confirm IO and SQL threads running.<\/li>\n
  Make sure seconds_behind_master is “0<\/strong>” to confirm MySQL replication is reliable on Slave (lagging too long is serious problem to solve when flipping Slave to new Master when original Mater crashes)<\/li>\n
  Closely monitor Last_IO_Error, Last_IO_Error, Last_SQL_Error and Last_SQL_Errorno to avoid MySQL slave outage<\/li>\n<\/ul>\n
  How can you add more Slaves to the Master ?<\/h3>\n
  To add more slaves to the Master, We will be using Percona XtraBackup<\/em>\u00a0to clone the slave which is already operational. The following steps explain about adding more slaves:<\/p>\n
  Do a full-backup on the Slave:<\/p>\n
  MinervaDBSlave$ xtrabackup --user=bkpuser --password=bkpusrPa55Wd --backup --slave-info --target-dir=\/Disk5\/Bkp\/data-backupdir\/<\/pre>\n
  When we use –slave-info<\/em> , Percona XtraBackup<\/strong> creates additional file called xtrabackup_slave_info<\/em><\/p>\n
  Apply the logs:<\/h4>\n
  MinervaDBSlave$ xtrabackup --prepare --use-memory=2G --target-dir=\/Disk5\/Bkp\/data-backupdir\/<\/pre>\n
  Copy the directory from MinervaDBSlave to MinervaDBNewSlave ( new slave)<\/p>\n
  Note: Please confirm mysqld in MinervaDBNewSlave (new slave) instance is shutdown before copying the contents of snapshot into its datadir\u00a0<\/em><\/span><\/p>\n
  rsync -avprP -e ssh \/Disk5\/Bkp\/data-backupdir\/ MinervaDBNewSlave:\/var\/lib\/mysql\/<\/pre>\n
  Grant privileges on the Master for new slave added<\/h4>\n
  MinervaDBMaster|mysql> GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'MinervaDBNewSlave(new slave IP)' IDENTIFIED BY 'MinervaDBNewSlavePa55WD';<\/pre>\n
  Copy the configuration file from MinervaDBSlave instance:<\/p>\n
  MinervaDBNewSlave$ scp root@MinervaDBSlave:\/etc\/mysql\/my.cnf \/etc\/mysql\/my.cnf<\/pre>\n
  Please don’t forget to change the server-id variable in \/etc\/mysql\/my.cnf to 103 and disable the replication on start:<\/p>\n
  [mysqld]\r\nserver-id=103<\/pre>\n
  Please restart mysqld after setting server-id<\/p>\n
  Configuring new slave (\u00a0MinervaDBNewSlave instance ) to connect with Master ( MinervaDBMaster instance) to start replication:<\/p>\n
  You can view MySQL master log file<\/strong> and position<\/strong> from\u00a0xtrabackup_binlog_pos_innodb <\/span><\/em>of the new slave<\/span>:<\/p>\n
  MinervaDBNewSlave|mysql> CHANGE MASTER TO\r\n                   MASTER_HOST='$MinervaDBMasterip',\r\n                   MASTER_USER='repl',\r\n                   MASTER_PASSWORD='MinervaDBNewSlavePa55WD',\r\n                   MASTER_LOG_FILE='mysql-bin.000409',\r\n                   MASTER_LOG_POS=336216351;<\/pre>\n
  Start the new slave:<\/p>\n
  MinervaDBNewSlave|mysql> START SLAVE;<\/pre>\n
  We can conclude the entire exercise is over and successful if IO<\/strong> and SQL threads<\/strong> are running when you check the NewSlave \/ MinervaDBNewSlave instance is replicating with Master.<\/p>\n
  Conclusion<\/h3>\n
  We recommend replication solution for \u00a0every customer to avoid MySQL from being a single-point-of-failure. Data is business, Reliable MySQL database infrastructure operations is a key component to build highly available and fault-tolerant 24*7 datanomy business. Splitting READ-WRITEs across MySQL Master – Slave is \u00a0an easiest method to build horizontally scalable MySQL Operations. If you are interested to know more about building optimal, scalable, highly available, fault-tolerant and self-healing database infrastructure operations on MySQL, MariaDB, PostgreSQL and ClickHouse, please book your appointment with MinervaDB Principal below for an no obligation 30 minutes free consulting.<\/p>\n
  Book your Appointment with a MinervaDB Principal <\/div><\/div>