Archive for the “MySQL” Category

Recently had to dump a mysql database that was larger than the available space. Here’s a quick one-liner to compress the dump and pipe it over ssh to another destination.

# mysqldump DATABASE | gzip -c –fast |ssh user@destination.domain.com ‘cat > /home/user/DATABASE.sql.gz’

Comments 1 Comment »

This is a quick setup of MySQL replication, but only works if both server are new builds with no dataset

On the master:

1. Put the following in my.cnf in the [mysqld] section:

server-id=1
log-bin=master-bin

2. Run the following one-liner:

# /etc/init.d/mysqld restart; mysql -e “GRANT REPLICATION SLAVE ON *.* TO ‘$user’@'$slave_ip_addr’ IDENTIFIED BY ‘$password’;”; mysql -e “RESET MASTER;”

On the slave:

1. Put the follwing in my.cnf in the [mysqld] section:

server-id=2

2. Run the follwoing one-liner:

/etc/init.d mysqld restart; mysql -e “CHANGE MASTER TO MASTER_HOST=’$master_ip_addr’, MASTER_USER=’$user’, MASTER_PASSWORD=’$password’, MASTER_LOG_FILE=’master-bin.000001′, MASTER_LOG_POS=98;”; mysql -e “START SLAVE;”

Comments No Comments »

This was a question posed to me today. The short answer is ‘no’.

Luckily, the Percona group was kind enough to do the benchmarks on performance logging. You can find more information here:

Impact of logging on MySQL’s performance

Comments No Comments »

Often times replication gets hosed and you have to skip a bad record. It’s easy to use:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

But what if you have an undetermined number due an upgrade of mysql? Luckily Maatkit has a nice little script that will increment skip counter by one until it no longer sees an error. Simply grab the perl script and run it as so:

# wget http://maatkit.org/get/mk-slave-restart

# perl mk-slave-restart –verbose

Once the errors are done, you can ctrl-c out of the script and check the slave status.

Comments No Comments »

There are two ways you can go about resetting the MySQL root password.

Option 1

Run the following commands:

# /etc/init.d/mysqld stop
# mysqld_safe –skip-grant-tables


Now you will login to to MySQL and run the following commands:

mysql> UPDATE mysql.user SET Password=PASSWORD(‘newpassword’) WHERE User=’root’ and Host=’localhost’;
mysql> flush privileges;


Quit MySQL and run the following commands:


# killall mysqld
# /etc/init.d/mysqld star


Option 2

edit /etc/my.cnf and add the following under the [mysqld] section:

skip-grant-tables

restart MySQL


# /etc/init.d/mysqld restart

Login to mysql and run the following commands:


mysql> UPDATE mysql.user SET Password=PASSWORD(‘newpassword’) WHERE User=’root’ and Host=’localhost’;
mysql> flush privileges;

Quit MySQL and remove the line ’skip-grant-tables’ from my.cnf and then restart MySQL.

Comments 1 Comment »