mysqldump -u root -p
mysql -u root -p
Tag Archives: mysql
Replication
On Primary:
– Lock tables:
FLUSH TABLES WITH READ LOCK;
keep this window open
– on another screen:
SHOW MASTER STATUS;
mysql-bin.000088 | 338004
On Secondary:
– stop mysql
On primary:
rsync -Sa –progress –exclude=mastername* –exclude=master.info –exclude=relay-log.info /var/lib/mysql/* <replicant>:/var/lib/mysql
On Secondary:
– start mysql
– start replication:
CHANGE MASTER TO
MASTER_HOST=’ip’,
MASTER_USER=’mysql_backup’,
MASTER_PASSWORD='<pass>’, MASTER_LOG_FILE=’mysql-bin.000088′, MASTER_LOG_POS=338004
On primary:
– close original window
Adding Mysql Replication On New Server
Update: relay-log.info no longer exists, you need to use SHOW MASTER STATUS;, see https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterstatus.html
How to set up a new Mysql replicator, based heavily on this blogpost
– Rsync database from existing secondary server (source):
rsync -Sa --progress --exclude=mastername* --exclude=master.info --exclude=relay-log.info /var/lib/mysql/* new_server:/var/lib/mysql
– pause replication on original secondary server
mariadb> stop slave
– cat relay-log.info
./mysqld-relay-bin.000875
208022
mysql-bin.000294
445713389
2
4
– re-rsync
– resume replication on original secondary server
mariadb>start slave
– disable replication on new server by adding ‘skip-slave-start’ to my.cnf
– start mariadb on new server
– set relay bin and mysql bin data on new server
# mysqlbinlog --start-position=208022 ./mysqld-relay-bin.000875 | mysql -p
mariadb> CHANGE MASTER TO MASTER_HOST='
– start replication
mariadb> start slave
– check replication status
mariadb> show slave status\G
– remove skip-slave-start from my.cnf
– enjoy!
HyperDB
HyperDB looks neat:
Basically a way to split up WordPress requests among multiple database servers. So you could have all writes go to your primary, and have reads split among your replicating and primary servers.