Category

Server Admin

Server Management

Add additional database to MySql Slave replication

By Server Admin No Comments

1) MASTER – restart the master with new binlog-do-db | remove all binlog-do-db in order to start populating the binary log with database data

2) SLAVE – Check the slave Database’s replication is up to date (SHOW SLAVE STATUS > Seconds_Behind_master). If not wait till it is

3) MASTER – FLUSH TABLES WITH READ LOCK; To stop new data entering the master

4) SLAVE – Check replication is fully caught up and no data is replicating (read_master_log_pos and exec_master_log_pos should be the same and not changing (in show slave status;))

5) SLAVE – STOP SLAVE; to stop any data replicating into the database once you unlock the master shortly.

6) Run MySQLDUMP with –single-transaction option

7) Once MySQLDUMP has started running unlock the master Database UNLOCK TABLES; In this way your systems can continue reading and writing to the master, hopefully keeping downtime to a minimum

8) Once MySQLDUMP has completed, import it into the slave Database

9) check the newly imported database looks correct

10) Restart the slave Database, adding replicate-do-db=database to the my.cnf file as you go

Centos 7 / Mysql – Too many open files error

By Server Admin No Comments

Even after raising the limit in ‘/etc/security/limits.conf’ and restarting the system, I was getting the ‘Too many open files’ error.

The solution that worked –

edit /usr/lib/systemd/system/mysqld.service and add

LimitNOFILE=infinity
LimitMEMLOCK=infinity

run systemctl daemon-reload and systemctl restart mysql.service