Tag: mysql

This article is part of a series of setting up MySQL replication. As with most things, there is always more than one way to do something. In the case of setting up MySQL replication, or rebuilding it, some options are better than others depending on your use case.

The articles in the series are below:
Setting up MySQL Replication using mysqldump
Setting up MySQL Replication using Percona XtraBackup
Setting up MySQL Replication using Rsync
Setting up MySQL Replication using LVM snapshots

This guide will document how to setup MySQL Master / Slave Replication using mysqldump.

MySQL Master / Slave Replication is useful for a number of situations, such as:
– Perform hourly backups on the Slave without locking the Master database
– Provide a degree of redundancy as you can promote the Slave
– Splitting the reads between the Master and Slave

Setting up MySQL Replication is pretty straight forward. Keep in mind that this guide requires you use mysqldump to create a backup of your existing databases, which will lock your tables while it runs. So be sure to schedule it accordingly!

Setup the Master MySQL server

Configure the my.cnf as shown below:

log-bin=/var/lib/mysql/db01-binary-log
expire-logs-days=5
server-id=1

Then restart MySQL to apply the settings:

# CentOS / RHEL:
[root@db01 ~]# service mysqld restart

# Ubuntu / Debian:
[root@db01 ~]# service mysql restart

Finally, grant access to the Slave so it has access to communicate with the Master:

mysql> GRANT REPLICATION SLAVE ON *.* to 'repl’@’10.x.x.x’ IDENTIFIED BY 'your_password';

Setup the Slave MySQL server

Configure the my.cnf as shown below:

relay-log=/var/lib/mysql/db02-relay-log
relay-log-space-limit = 4G
read-only=1
server-id=2

Then restart MySQL to apply the settings:

# CentOS / RHEL:
[root@db02 ~]# service mysqld restart

# Ubuntu / Debian:
[root@db02 ~]# service mysql restart

Finally, prep the slave server to connect to the Master MySQL server by:

# For MySQL 5.0 and 5.1
mysql> STOP SLAVE; RESET SLAVE;

# For MySQL 5.5 and 5.6
mysql> STOP SLAVE; RESET SLAVE ALL;

# Then run this command for all MySQL versions
mysql> CHANGE MASTER TO MASTER_HOST=’10.x.x.x’, MASTER_USER='repl', MASTER_PASSWORD='your_password'

RESET SLAVE will remove any previously configuration replication settings. Also, the MASTER_LOG_FILE and MASTER_LOG_POS is not needed as the following section will be dumping the databases with the –master-data flag.

Importing the databases onto the Slave MySQL server

A dump of the Master MySQL server is needed in order to get the Slave MySQL server in sync. Please note that this will lock your tables while this runs!

Perform this on the master server by:

[root@db01 ~]# mysqldump --all-databases --master-data | gzip -1 > /root/all.sql.gz

Now transfer over the database dump to the Slave MySQL server:

[root@db01 ~]# scp /root/all.sql.gz [email protected]:/root/

On the MySQL Slave server, import the dump by running:

[root@db02 ~]# zcat /root/all.sql.gz | mysql
[root@db02 ~]# mysql
mysql> FLUSH PRIVILEGES;
mysql> quit

As this dump contains the internal database ‘mysql’, all the passwords, including root, will be the same as they are on the Master MySQL server. So be sure to update /root/.my.cnf with the proper credentials:

[root@db02 ~]# vi /root/.my.cnf
[client]
user=root
password=db01_root_mysql_password

Also be sure to update any passwords your database backup scripts may use to reflect the new credentials!

Start and test replication

Start replication on the Slave MySQL server by:

[root@db02 ~]# mysql
mysql> START SLAVE;

And verify replication is online and working by confirming the following output:

mysql> SHOW SLAVE STATUS\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
...

If those values are the same as what is shown above, then replication is working properly! Perform a final test by creating a test database on the Master MySQL server, then check to ensure it shows up on the Slave MySQL server. Afterwards, feel free to drop that test database on the Master MySQL server.

From here, you should be good to go! Just be sure to setup a monitoring check to ensure that replication is always running and doesn’t encounter any errors. A very basic MySQL Replication check can be found here:
https://github.com/stephenlang/system-health-check

If your queries are constantly writing to disk, and you cannot fix your query (which is strongly recommended) or properly adjust your buffer sizes for one reason or another, you can create a memory backed file system for the tmpdir, which should allow for incredibly faster writes and reads.

This should only be viewed as a temporary fix until you can get your query to stop writing its temp tables to disk.

Create the mount point:

mkdir -p /tmp/mysqltmp

Now give MySQL access to it:

chown mysql:mysql /tmp/mysqltmp

Set up the entry in /etc/fstab so it will mount on reboot:
* NOTE: Change the uid and gid to whatever MySQL runs as in /etc/passwd and /etc/group

vi /etc/fstab
# Add
tmpfs /tmp/mysqltmp tmpfs
rw,gid=27,uid=27,size=256M,nr_inodes=10k,mode=0700 0 0

Mount the memory backed file system:

mount -a

Update /etc/my.cnf and add the variable:

tmpdir = /tmp/mysqltmp

Now, restart MySQL:

service mysql restart

Finally, confirm MySQL is now utilizing the new memory backed filesystem:

mysql
mysql> show variables like '%tmpdir%';
+-------------------+----------------+
| Variable_name     | Value          |
+-------------------+----------------+
| slave_load_tmpdir | /tmp           |
| tmpdir            | /tmp/mysqltmp  |
+-------------------+----------------+
2 rows in set (0.00 sec)

Ever wonder what the exact queries are that cause MySQL to spike your CPU constantly? Here is an quick and easy way to enable the general log within MySQL without restarting MySQL.

First, confirm your initial variables before changing anything:

mysql> show variables like '%general%';
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| general_log      | OFF                      |
| general_log_file | /var/lib/mysql/web01.log |
+------------------+--------------------------+
2 rows in set (0.00 sec)

So in this case, the logs will be recorded to /var/lib/mysql/web01.log. If you have nothing set for ‘general_log_file’, you can set it as shown below, but just make sure its in a directory that MySQL can write to:

mysql> set global general_log_file='/var/lib/mysql/web01.log';

With the log file ready, enable the general log by:

SET global general_log = 1;

Verify it is enabled by running:

mysql> show variables like '%general%';
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| general_log      | On                       |
| general_log_file | /var/lib/mysql/web01.log |
+------------------+--------------------------+
2 rows in set (0.00 sec)

You can now view the queries being ran by looking at your general_log_file. Please note, your log will be in a different location then the one I have below:

tail -f /var/lib/mysql/web01.log

On busy MySQL servers, leaving the general_log setting enabled will quickly cause your hard drive to fill up. So after a few seconds or minutes, disable it by running:

SET global general_log = 0;

Then verify the setting is now off by:

mysql> show variables like '%general%';
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| general_log      | OFF                      |
| general_log_file | /var/lib/mysql/web01.log |
+------------------+--------------------------+
2 rows in set (0.00 sec)

You can now go through your log file to see exactly what queries have been running.

Sometimes, MySQL replication may break, displaying an error in ‘show slave status’ such as:

mysql> show slave status\G
********************* 1. row *********************
                 Last_Errno: 1062
                 Last_Error: Error 'Duplicate entry '123456' for key 1' on query. Default database: 'XXX'. Query: 'YYY'
               Skip_Counter: 0

When you see this, you can try to increment the counter by:

mysql> stop slave;
mysql> set global sql_slave_skip_counter=1; start slave;

Then rerun the ‘show slave status’ again to see if replication is now working again. If you start seeing many of these, it could possibly indicate that your replication is not consistent between the Master and Slave MySQL servers, and you may need to consider rebuilding replication.

Resetting a forgotten MySQL root password is a pretty straight forward task to complete assuming you have sudo or root access to the server. It is important to note that by performing this procedure, MySQL will be down till you complete everything. So be sure to do this during a time when it will not impact your business.

First, stop the MySQL service

service mysqld stop # RHEL clones
service mysql stop # Ubuntu / Debian distro

Now its time to bring MySQL back up in safe mode. This means we will start MySQL, but we are simply skipping the user privileges table:

sudo mysqld_safe --skip-grant-tables &

Time to log into MySQL and switch to the MySQL database:

mysql -uroot
use mysql;

Now reset the root password and flush privileges:

update user set password=PASSWORD("enternewpasswordhere") where User='root';
flush privileges;
quit

Once you have completed that, its time to take MySQL out of safe mode and start it back up normally. NOTE: Be sure MySQL is fully stopped before trying to start the service again. You may need to kill -9 the process if its being stubborn.

Stop MySQL:

service mysqld stop # RHEL clones
service mysql stop # Ubuntu / Debian distro

Verify that the MySQL process is no longer running. If they are, you may have to kill -9 the process:

ps -waux |grep mysql

Start MySQL back up:

service mysqld start # RHEL clones
service mysql start # Ubuntu / Debian distro

Finally, test out logging in to ensure its now working properly:

mysql -uroot -p