MySQL Replication Rumi, June 27, 2011 MySQL is the relational database system of choice for open sourcers. Replication is the process of replicating data from one MySQL database server (the master) into another (the slave). We’ll go into why you would want to replicate a MySQL database in another article. MySQL Replication Using the master-slave configuration mentioned above, only the changes made to the master are replicated in the slave. Changes made to the slave do not affect the master. If you follow the steps below, you can set up MySQL replication in a matter of minutes. 1.Open the my.cnf or my.ini file (depending on whether you are running Linux or Windows). 2.Type in the following, somewhere below “[mysqld]” log-bin=mysql-bin server-id=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 3.Restart MySQL on the master server. 4.Create a new user on the master server with the “REPLICATION SLAVE” privelege. You don’t need to assign any other priveleges to this user. In the following commands, replace X.X.X.X with the IP address of the slave server. CREATE USER ‘user’@ ‘X.X.X.X’ IDENTIFIED BY ‘password’; GRANT REPLICATION SLAVE ON *.* TO ‘user’@'X.X.X.X’ IDENTIFIED BY ‘password’; 5.Execute ‘FLUSH TABLES WITH READ LOCK;’ on the master to prevent writing to the databases. 6.Execute ‘SHOW MASTER STATUS;’ on the master and note down the values because we’ll use these later. 7.Execute ‘UNLOCK TABLES;’ on the master. 8.Open the my.cnf or my.ini on the slave server. 9.Enter somewhere below “[mysqld]” on the slave server: server-id=2 10.Save the file and restart mysqld. 11.Execute the following on the slave server (adjust values according to user setup in step 4 and values retrieved from step 6): CHANGE MASTER TO MASTER_HOST=’X.X.X.X’, MASTER_USER=’user’, MASTER_PASSWORD=’password’, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000001?, MASTER_LOG_POS=98, MASTER_CONNECT_RETRY=10; 12.Execute the following on the slave server: START SLAVE; 13.Check the mysql log on the slave to ensure that the connection to the master has been successful. You should see a line similar to the following: 091104 8:42:02 [Note] Slave I/O thread: connected to master ‘root@X.X.X.X:3306?, replication started in log ‘mysql-bin.000001? at position 98 Now you should have successfully configured master-slave MySQL replication. Something you might want to consider is limiting the binary log files retentions with the expire_logs_days on the master, otherwise the replicated date will just grow and grow Src: http://www.hackosis.com/how-to-setup-mysql-replication/ Related MySQL MySQL
Delete/Purging mysql-bin (binlog) files safely January 7, 2021 The file mysql-bin.index keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.index are: PURGE BINARY LOGS TO ‘binlogname’; PURGE BINARY LOGS BEFORE ‘datetimestamp’; These will clear all binary logs before the binlog or timestamp you just… Read More
Exporting Percona Database March 11, 2020March 11, 2020 Exporting percona database is however a bit tricky way to make funcitonal. The usual- mysqldump -u root -p [database] > database.sql will stuck and shoot you many error. All you need is the add an extended parameters to execute the dump, and here it is: mysqldump -u root -p [database]… Read More
Install Mydumper Myloader on Centos 7 June 11, 2020July 4, 2020 If you want to execute logical backups using an alternative to mysqldump that works with parallel threads giving faster execution times mydumper is the correct tool. wget https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el7.x86_64.rpm In order to use mydumper and myloader you can use the following sample commands: For dumping/exporting database: mydumper -u <username> -p <password>… Read More