I got the following Nagios error after setting up replication between two MySQL 5.6 servers and monitioring the slave. "UNKNOWN: should never reach this part" This plugin was preivously working on MySQL 5.5 servers. It appears that the MySQL 5.6 replication has a different output from MySQL 5.5. Therefore you will need to download the latest Read More…
Category: MySQL
Creating an Extra MySQL Slave from Another MySQL Slave
I was tasked with creating a new MySQL slave in a Brighton datacenter to replicate with the master in London. There was already a replication in place from London to Manchester. Here are the steps to copy the information from Manchester Slave (MySQL2) to the Brighton Slave (MySQL3) . This method allows the master MySQL to be completely untouched In Read More…
Move MySQL Directory in WHM \ cPanel
We originally installed an image from the cPanel website. The /root parition holds the system information and the /data houses the users web and mail data. Unfortunatley the /root partition is NOT installed as an LVM which makes it difficult to expand. What I did notice is that the users MySQL data is actually stored in the /root Read More…
Trimming MySQL bin logs
During MySQL replication, changes on the Master are written to binary logs for the Slave(s) to update. By default these binary logs will continue to grow indefinitely unless you apply a specific setting. In this scenario I have chosen to keep 4 days worth of bin logs. The amount of bin logs is dependant on how much lag you Read More…
Configuring MySQL Replication 5.5 in 6 steps
Scenario: You want to create a MySQL replication between an exisiting master and a brand new slave. Both machines running FreeBSD 8.x with MySQL 5.5. The master server currently has a mixture of InnoDB and MyISAM engines. A small amount of downtime is available. Master server: 10.1.1.1 Slave server: 10.1.1.2 (1) ON Read More…
Using Logrotate with MySQL Database Backups
Scenario: I wanted to backup all the databases individually from a MySQL server and keep for x days. I set about achieving this using a custom script combined with logrotate. The server is running FreeBSD 8.x. 1) Firstly, the script (backup-all-dbs.sh): #!/usr/local/bin/bash # backup-all-dbs.sh # backup each mysql db into a different file, rather Read More…
Backup MySQL Database Daily and Keep for 7 days
Here is a script I quickly wrote to mysqldump a single database and keep 7 days worth of backups. #!/bin/bash # Script – mysqlbackup.sh # Created by Jordansphere – 20/Jun/13 DATADIR=”/home/mysqlbackups” USERNAME=username PASSWORD=password NOW=$(date +”%d-%m-%Y”) #BACKUP DATABASE in date format (eg “MyDatabase-20-6-2013.sql”) /usr/bin/mysqldump -u $USERNAME -p$PASSWORD –databases MyDatabase > /home/mysqlbackups/MyDatabase.$NOW.sql # REMOVE BACKUPS MORE THAN Read More…
MySQL Corruption: Error 1034 (HY000) : Incorrect key file for table ‘xyz’ ; try to repair it
Scenario: We upgraded from MySQL 5.1 to 5.5 (It was 4.1 in the past and possibly even 3.x before my time!). There seems to be some sort of corruption in a particular DB. The normal myisamchk and REPAIR TABLE functions didnt cut the mustard. Below are my steps to fix the issue. It appears that the Read More…
MySQL : mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication
PHP 5.3 / Mysql 5.5 / FreeBSD You get the error message: Warning: mysql_connect() [function.mysql-connect]: Premature end of data (mysqlnd_wireprotocol.c:553) in C:\directorya\directoryb\dbconnect.phpon line 57 Warning: mysql_connect() [function.mysql-connect]: OK packet 1 bytes shorter than expected in C:\directorya\directoryb\dbconnect.php on line 57 Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please Read More…
Setting up a daily cron job to mysqldump
Here is a a little life saver to dump a MySQL DB via cron and zip it up in date format 0 2 * * * root mysqldump -u root -pPASSWORD databasename | gzip > /tmp/DBBACKUPS/database_`date ‘+%Y-%m-%d’`.sql.gz This runs at 2am every morning. You can then set up another cron to delete old files or use logrotate to save Read More…
You must be logged in to post a comment.