Multiple MySQL databases on one binary install using mysqld_multi

One very cool (and often overlooked) feature of mysql is the use of mysqld_multi.

This script allows you to run multiple instances of MySQL server on a single box with one set of MySQL server binaries. You’ll just need to specify different configuration parameters (port, socket, log file locations, etc.) in your my.cnf file. I’ll go through how simple this can be so setup below.

First, you’ll need to create a new user that will exist on all server instances and will only need the SHUTDOWN privilege. You can probably guess this, but this user is needed to initiate a shutdown on the specified server. Since you will have multiple instances running on one server, the default init scripts will no longer work to shutdown anything but the default instance.

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'my-pwd';

Next, we will need to edit our my.cnf file. The first difference here is the general layout. First, you will have a section for [mysqld_multi], then each section after will be for the specified mysql instance. For example, if you have 3 sections defined – [mysqld1], [mysqld2], [mysqld3] – you will have places to configure 3 different mysql databases.

The [mysqld_multi] section is very simple, it just defines the mysqld_safe and mysqladmin binaries as well as the username and password that we previously created for shutting down the server. For example, it may look something like this:

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user    =       multi_admin
password =      my-pass

Next, we can begin defining our first database. I like to define all of the following as a minimum to get the server up and running. All other variables not defined will use the defaults given to it.

[mysqld1]
datadir=/var/lib/mysql
tmpdir=/tmp/mysqltmp
socket=/var/lib/mysql/mysql.sock
pid-file=/var/lib/mysql/mysql.pid
log-bin=/var/lib/mysql_logs/binlogs/dbsvr01-binlog
relay-log=/var/lib/mysql_logs/relaylogs/dbsvr01-relaylog
slow-query-log-file = /var/lib/mysql_logs/dbsvr01.slow_queries.log
log-error=/var/lib/mysql_logs/dbsvr01.mysql.err

That’s it! Your first database is ready to go. I wouldn’t recommend leaving all other variables at their default, so be sure to know what parameters you want to configure and which ones you can leave alone.

For safety, I usually delete the default mysql init script and replace it with one that will utilize the multiple databases. The MySQL RPM will provide this for you here: /usr/share/mysql/mysqld_multi.server. Copy this to the init run level of your choosing and don’t forget to make the necessary changes to chkconfig.

A little bit about the mysqld_multi.server script. By running the command you will quickly find that there are only 4 parameters to choose from:

Usage: /etc/init.d/mysqld_multi.server {start|stop|report|restart}

Issuing a “start” followed by a number (in our example 1) will start the first database. If you then wanted to start the second database, issue a “mysqld_multi.server start 2”. The same logic carries over for ‘stop’ and ‘restart’. The ‘report’ option is similar to ‘status’ in other common init scripts. This will give you a list of all of the mysql database instances that are defined, followed by if they’re started or stopped. For example, If I have 3 instances all defined and running it will look something similar to this:

[joe@DBSVR01 /]$ /etc/init.d/mysqld_multi.server report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running

So there you have it! Enjoy :)

More Info: http://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html

Rebuilding a MySQL Slave

This is the general process to rebuild a slave. In this example, we are rebuilding a database called DBX03.
Stop MySQL on DBX03 and remove all of the current data.  Depending on the reason for the rebuild, you may also want to remove the mysql database folder. Wiping clean the /DBDATA/mysql/* files would do the trick.
Grab the most recent snapshot of the data from another server. Preferably a slave and we would rather not take a snapshot off of a master db due to the increased disk utilization as we copy the data off. If the database server that you’re rebuilding takes frequent snapshots, you may also use that data (we assume that DBX03 does not take any snapshots). Note the SLAVE STATUS output from the snapshot script. This information is mandatory to start the slave at the correct position
Mount the snapshot and copy over the data to DBX03. (rsync -azvpP –stats –delete slave-svr:/mnt/DB_SNAP/ /DBDATA/mysql/ ). It would be smart to stop the snapshot script from running again so it doesn’t interfere with your work.
Once the rsync is done, on DBX03, you need to cleanup the unnessesary files. These include: relaylog files, error log, master.info, mysql.pid, mysql.sock, binlog files, relay-log.info, slow query logs, etc
Check that the permissions are correct for all files and folders (mysql should be the user and group owner). Ensure that the proper log folders have been created (defined in my.cnf)
Start MySQL on DBX03
Tail the end of the error log to ensure that MySQL starts up properly. It should take a few minutes to replay the innodb logs (assuming that the snapshot wasn’t taken while the database was shutdown). This will also notify you immediately if any necessary files or folders are missing so read the output carefully
If all started up correctly and the necessary logs have been replayed on DBX03 it’s time to configure the slave. This information is necessary for this to be configured correctly and can be found in the slave status of the snapshot log. For example, the slave status section of the snapshot log should look like this if taken off of a slave:

mysql> show slave status;
 stdClass Object
 (
 [Slave_IO_State] =>
 [Master_Host] => master.mydomain.net
 [Master_User] => repl_usr
 [Master_Port] => 3306
 [Connect_Retry] => 60
 [Master_Log_File] => master-binlog.001439
 [Read_Master_Log_Pos] => 899027494
 [Relay_Log_File] => slave-relaylog.004518
 [Relay_Log_Pos] => 899027022
 [Relay_Master_Log_File] => master-binlog.001439
 [Slave_IO_Running] => No
 [Slave_SQL_Running] => No
 [Replicate_Do_DB] =>
 [Replicate_Ignore_DB] => mysql
 [Replicate_Do_Table] =>
 [Replicate_Ignore_Table] =>
 [Replicate_Wild_Do_Table] =>
 [Replicate_Wild_Ignore_Table] =>
 [Last_Errno] => 0
 [Last_Error] =>
 [Skip_Counter] => 0
 [Exec_Master_Log_Pos] => 899026874
 [Relay_Log_Space] => 899027841
 [Until_Condition] => None
 [Until_Log_File] =>
 [Until_Log_Pos] => 0
 [Master_SSL_Allowed] => No
 [Master_SSL_CA_File] =>
 [Master_SSL_CA_Path] =>
 [Master_SSL_Cert] =>
 [Master_SSL_Cipher] =>
 [Master_SSL_Key] =>
 [Seconds_Behind_Master] =>
 [Master_SSL_Verify_Server_Cert] => No
 [Last_IO_Errno] => 0
 [Last_IO_Error] =>
 [Last_SQL_Errno] => 0
 [Last_SQL_Error] =>
 [Replicate_Ignore_Server_Ids] =>
 [Master_Server_Id] => 1001
 )

These values can be used to populate the required data to start the slave.

CHANGE MASTER TO MASTER_HOST = '<master_host_ip>'; #IP of the master db server which will send binlogs
CHANGE MASTER TO MASTER_PORT = <master_host_port>; #Port of the master db server
CHANGE MASTER TO MASTER_USER = '<master_host_repl_username>'; #username as defined on master db
CHANGE MASTER TO MASTER_PASSWORD = '<master_host_repl_pwd>'; #password as defined on master db
CHANGE MASTER TO MASTER_LOG_FILE = '<[Master_Log_File]>'; #binlog name on master, as defined above
CHANGE MASTER TO MASTER_LOG_POS = <[Exec_Master_Log_Pos]>; #binlog location to start at. If new binlog, pos=0 works

Perform a SHOW SLAVE STATUS to confirm that the settings are all correct
Perform a START SLAVE to start the slave
You should know immediately if the slave crashes or not. The slave status will calculate the time it’s behind master and report back. If there’s a duplicate key issue then you most likely started the slave in the wrong spot but investigating will be needed to verify. You can also tail the MySQL error log to view any additional errors.

Switching MySQL Masters

In the event of a disaster happening to a master db, the last resort is to fail back that master to a slave. This should be an absolute last resort due to the complexity of the issue.
This could also be used as a method to switch masters for a big database change, such as adding an index to a table. This method can be used to minimize downtime from such a big change.

The first variable that needs to be noted is “log-slave-updates”
http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_log-slave-updates
When log-slave-updates=1, the slave is going to log ALL queries that come to it because it’s a slave in it’s binlog. In this case, you will need to be more careful. If promoting this slave to a master, you need to make 100% sure that no more updates are going to come through to it as a slave. Resetting the slave status and removing the slave configuration is probably the easiest way to ensure this. If you want to daisy-chain a slave off of another slave server, this paramater must be set so all replicated queries from the original master can trickle down the chain.
When log-slave-updates=0, only queries for this specific server will be logged in the server’s binlog. This is most convenient when needing to quickly set this slave as the master and then point another slave to it because the only updates being logged to the binlog are those that are actually executed on this server without it being a slave.

Now, to actually promoting new masters…
In this scenario, we will pretend that the master db is corrupt and we now need to promote a new one.
First, check all slaves to see which one is the most caught up. If they are all caught up, then determine which one could be used with additional slaves attached to it the quickest (eg. log-slave-updates=1 as mentioned above). If a slave has another slave running off of it already, this is perfect.
Make sure that the slave that you’re about to promote isn’t read-only (it should be read-only until you change it). Ensure this change is also made in the my.cnf file so the variable is set the next time this instance gets restarted.
Next I usually shut down the master mysql instance for safety.
For  sanity, I issue a “FLUSH LOGS” statement on the new master. This will roll the server to a new, fresh binlog which will be easy to start new slaves from. Take note of this new binlog that was just rolled to.
Now change all hosts files to set the new MASTER-IP record
If a new slave is already up and running from this db, then also change the hosts entry for SLAVE
Assuming that all other slaves were caught up and at the same position as this one, reset those slave’s configuration now pointing to the newly promoted master and the binlog that you just started. If the slave isn’t caught up, give it some time to catch up and get to the correct position before issuing these commands. If this isn’t followed exactly you will run into duplicate key issues and/or missing and inconsistent data

——————————————————————————

In the case that you just want to switch a slave’s master (eg. the master didn’t die), follow these steps. In this example I used DB02:3307 and DB02:3306 databases
I switched DB02:3307’s master to be DB02:3306. The process for doing this without needing to rebuild the entire slave is very sensitive, but only requires a few steps. Assume that DB02:3307 is currently replicating off of a server called DB01. Now, if DB01 completely dies, these slaves won’t have to be rebuilt and DB02 will become the master.

First, I set DB02:3306 and DB02:3307 slaves to stop at a the same specific binlog + pos:
mysql-DB02:3306> START SLAVE UNTIL MASTER_LOG_FILE = ‘db01-binlog.000722’, MASTER_LOG_POS=430000000;
mysql-DB02:3307> START SLAVE UNTIL MASTER_LOG_FILE = ‘db01-binlog.000722’, MASTER_LOG_POS=430000000;

Then, once the slaves stop I know that both of the slaves were at the exact same location. This is crucial.
The next step isn’t necessary, but I do it for ease and safety. On DB02:3306 I issue a “FLUSH LOGS;” statement which will close and reopen all log files. For the binlogs (which we are most interested in) it will start a new binlog so we can start at position 0 of the newest binlog on DB02:3306. Alternatively, you could just do a “SHOW MASTER STATUS;” to get the current binlog information for that host.
Once I’ve found and noted the new master binlog file name and position, I can start that slave back up.
Next, I go over to DB02:3307 to reset the configuration. This time with DB02:3306 as the MASTER_HOST, and the MASTER_LOG_FILE + MASTER_LOG_POS that were noted previously.

 

Helpful MySQL Commands, tips and tricks

I’m pretty much brain dumping now so I apologize if I leave open any questions. Post to the comments if you would like for me to go into further detail on any of these…

I would argue that all of these commands and tips need to be mastered to be an effective SysAdmin DBA.


The configuration formula… The estimated amount of memory that will be allocated to MySQL. This formula should be used when initially configuring a server while still leaving some for the OS (assuming that this is a dedicated MySQL database server).

KBS + IBP + (RBS + SBS + BLCS) * MC = Physical RAM – OS requirements

KBS -> Key Buffer Size
IBP -> InnoDB Buffer Pool size
RBS -> Read Buffer Size
SBS -> Sort Buffer Size
BLCS -> Binlog Cache Size
MC -> Max connections
If a DB server begins to use a lot of SWAP memory, this is very bad. The disk thrashing that is occurring as a result of the SWAP being used has a reputation of bringing the entire db to a halt. I have witnessed this in several environments. If a high transaction MySQL db is using SWAP memory, immediate action should be taken before it gets worse.


When this command is issued, statements won’t be logged to the binlog (or replicated) in this session.

mysql> SET SQL_LOG_BIN = 0;

The skip current slave query statement. BE CAREFUL! Skipping a slave statement without a FULL, in-depth investigation can be VERY damaging and cause inconsistencies that may lead to a full rebuild of that data set. If you’re skipping a statement on the slave be sure that you know what you’re doing!
With that said, this definitely can come in handy if a bad statement gets replicated and breaks a slave in your replication tree.

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

These are the commands to start up new slave. I’m assuming that you have the correct data in place. I will have a separate post on how to build a new slave with full details later.

CHANGE MASTER TO MASTER_HOST = '';
CHANGE MASTER TO MASTER_PORT = ;
CHANGE MASTER TO MASTER_USER = '';
CHANGE MASTER TO MASTER_PASSWORD = '';
CHANGE MASTER TO MASTER_LOG_FILE = '';
CHANGE MASTER TO MASTER_LOG_POS = ;

#Optional – using SSL to secure replication

CHANGE MASTER TO MASTER_SSL=1;
CHANGE MASTER TO MASTER_SSL_CA = '/ca-cert.pem';
CHANGE MASTER TO MASTER_SSL_CAPATH = '/ssl/';
CHANGE MASTER TO MASTER_SSL_CERT = '/client-cert.pem';
CHANGE MASTER TO MASTER_SSL_KEY = '/client-key.pem';
START SLAVE;

Issue a mysqldump only of the stored procedures

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt id > triggers.id.sql

Issue a mysqldump only of the table layout and schema (NO DATA)

mysqldump --all-databases --no-data > db_layout.sql

If you have a mysqldump file and want to insert it into a database, you can just pipe it like this:

mysql -h  -u  -p < file.sql

Issue a mysqldump for specific WHERE clause to output.sql file

mysqldump --where="id > 1" > output.sql

Drop partition then immediately re-add it

mysql> ALTER TABLE email_queue DROP PARTITION p01;
mysql> ALTER TABLE email_queue ADD PARTITION (PARTITION p07 VALUES IN (01));

Change the query cache size on the fly

mysql>set global query_cache_size=10*1024*1024;

Then flush the active query cache

mysql> flush query cache;reset query cache;

Decode row based relay logs. I’ll have another post on how to debug row based replication errors. They’re definitely not as straight-forward as statement based replication issues.

mysqlbinlog mysql_server-relay-bin.000005 --base64-output=decode-rows -v > decoded.log

Other Configuration Notes

  • innodb_log_buffer_size -> InnoDB only keeps data round log buggers for ~1 second
  • possibility to decrease disk use dramatically by innodb_flush_method=O_DIRECT and innodb_double_write=OFF
  • innodb_thread_concurrency = twice # of CPU’s. could save some for MyISAM if it’s used
  • innodb_data_file_path -> if not big enough, it could create too many fragments
  • mysqldumpslow slow_query.log -> full report of SQL statements in slow query log
  • sync_binlog = 1 will send the binlogs to slaves more frequently. potential to dramatically improve slave performance
  • OPTIMIZE = defragment
  • ANALYZE = rebuild index (sorting)
  • normalization=deduplication
  • myisam_sort_buffer_size -> not only for myisam. can be set at session, buffer is released when finished
  • max_heap_table_size -> same or more than tmp_table size
  • max_heap_table_size ->if using BLOB or TEXT datatypes, don’t go above 64Mb. start at
  • 16Mb. if no BLOB or TEXT, created_temp_disk_table should be equal to 0
  • ‘heap’ never goes to the SWAP. heap covers memory tables and tmp memory tables
  • key_buffer_size in mysql 5.0 doesn’t use more than 4Gb even if it’s assigned it
  • myisam_sort_buffer_size -> keep # high when doing analyze. can set this on the fly
  • convert TEXT to varchar to avoid tmp tables
  • FLUSH TABLES; will help to decrease the time for innodb to shutdown