{"id":1275,"date":"2012-07-31T15:15:08","date_gmt":"2012-07-31T19:15:08","guid":{"rendered":"http:\/\/mossiso.com\/?p=1275"},"modified":"2014-09-22T14:09:13","modified_gmt":"2014-09-22T18:09:13","slug":"making-multiple-mysql-instances-on-one-server","status":"publish","type":"post","link":"https:\/\/mossiso.com\/2012\/07\/31\/making-multiple-mysql-instances-on-one-server\/","title":{"rendered":"Making Multiple MySQL Instances on One Server"},"content":{"rendered":"

I’m trying this new idea for backing up our production MySQL servers. I have a backup server that basically runs rdiff-backup in the morning across several servers, but then does nothing for the rest of the day. It’s a pretty decent machine, so I’d like to utilize some resources. Replicating a MySQL server is a good way to ensure High Availability in case of a failure. The backup server acts as a slave to the master (production) server. Basically, the slave is an exact copy of the master. They are two separate instances of MySQL server running on two physical servers. Whatever queries run on the master are sent to the slave so it can do the same. This way they are kept completely in sync. You could also have the slave take over for the master, should the master server happen to fail.<\/p>\n

\"\"<\/a>
The slave is an ever updating duplicate of the master.<\/figcaption><\/figure>\n

The only problem I face with this set up, though, is that I have multiple production servers out there. So this only works if this backup server could be a slave for multiple machines.<\/p>\n

\"\"<\/a>
No slave can serve two masters.<\/figcaption><\/figure>\n

This is not possible, though, because, of course, no slave can serve two masters<\/a>. Fortunately, a server can have multiple instances of MySQL running on it! So, in a sense, we have a server with multiple MySQL instances, to which a master can replicate. More about that set up in an upcoming post.<\/p>\n

\"\"<\/a>
The slave has multiple instances of MySQL running.<\/figcaption><\/figure>\n

A how to on this blog<\/a>, shows how this can be done. I’ll replicate the process below.<\/p>\n

STEPS TO MULTIPLE MYSQL MADNESS<\/h2>\n

On the slave server<\/h3>\n

Step 1. Install MySQL<\/h4>\n

We’ll be working with CentOS 5.8, but this could really apply for any OS. First we’ll need to install MySQL like normal.<\/p>\n

yum install mysql mysql-server<\/pre>\n
There are plenty of good tutorials out there on how to install the specific version of MySQL you want on the specific OS you’re running.<\/p>\n
Step 2. Set up the data area.<\/h4>\n
You’ll need to have a different folder for each of the MySQL instances, say \/dbases\/master-a\/<\/code>, \/dbases\/master-b\/<\/code>, and \/dbases\/master-c\/<\/code>.<\/p>\n
mkdir -p \/dbases\/{master-a,master-b,master-c}<\/pre>\n
Step 3. Copy the default my.cnf file<\/h4>\n
This is the default MySQL config file, it may be named differently on other OSes.<\/p>\n
cp \/etc\/my.cnf \/etc\/master-a.cnf; cp \/etc\/my.cnf \/etc\/master-b.cnf; cp \/etc\/my.cnf \/etc\/master-c.cnf<\/pre>\n
Step 4. Edit the new MySQL config files.<\/h4>\n
For each new config file, you’ll need to specify some unique variables.<\/p>\n
\r\n[mysqld]\r\nport=3307\r\ndatadir=\/dbases\/master-a\r\nsocket=\/dbases\/master-a\/mysql.sock\r\nuser=mysql\r\nserver_id=3307\r\nlog-bin=\/dbases\/master\/mysql-bin.log\r\n\r\n# Disabling symbolic-links is recommended to prevent assorted security risks;\r\n# to do so, uncomment this line:\r\nsymbolic-links=0\r\n\r\n[mysqld_safe]\r\nlog-error=\/dbases\/master-a\/mysqld.log\r\npid-file=\/dbases\/master-a\/mysqld.pid\r\n<\/pre>\n
The port<\/code> option sets this MySQL instance on a different port than the default 3306. The datadir<\/code>, socket<\/code>, log-bin<\/code>, log-error<\/code>, and pid-file<\/code> options make sure the necessary files are not using the default files.<\/p>\n
Step 5. Create new init scripts.<\/h4>\n
The init script allows the server to start and stop the service at boot time, and allows for easy start up and shutdown (on CentOS\/RedHat, at least – with an easy service mysqld start<\/code>).<\/p>\n
cp \/etc\/init.d\/mysqld \/etc\/init.d\/mysqld-master-a<\/pre>\n
Just do one for now. We’ll copy the new one to create the others, then just do a quick search and replace in those files to change the master-a to master-b and master-c.<\/p>\n
Step 6. Edit the init script<\/h4>\n
\n
#!\/bin\/bash\r\n#\r\n# mysqld        This shell script takes care of starting and stopping\r\n#               the MySQL subsystem (mysqld).\r\n#\r\n# chkconfig: - 64 36\r\n# description:  MySQL database server.\r\n# processname: mysqld\r\n# config: \/etc\/master-a.cnf<\/span>\r\n# pidfile: \/dbases\/master-a\/mysqld.pid<\/span>\r\n\r\n# Source function library.\r\n. \/etc\/rc.d\/init.d\/functions\r\n\r\n# Source networking configuration.\r\n. \/etc\/sysconfig\/network\r\n\r\nprog=\"MySQL\"\r\n\r\n# extract value of a MySQL option from config files\r\n# Usage: get_mysql_option SECTION VARNAME DEFAULT\r\n# result is returned in $result\r\n# We use my_print_defaults which prints all options from multiple files,\r\n# with the more specific ones later; hence take the last match.\r\nget_mysql_option(){\r\n        result=\/usr\/bin\/my_print_defaults \"$1\" | sed -n \"s\/^--$2=\/\/p\" | tail -n 1<\/code>\r\n        if [ -z \"$result\" ]; then\r\n            # not found, use default\r\n            result=\"$3\"\r\n        fi\r\n}\r\n\r\nget_mysql_option mysqld datadir \"\/dbases\/master-a<\/span>\"\r\ndatadir=\"\/dbases\/master-a<\/span>\"\r\nget_mysql_option mysqld socket \"\/dbases\/master-a\/mysql.sock<\/span>\"\r\nsocketfile=\"\/dbases\/master-a\/mysql.sock<\/span>\"\r\nget_mysql_option mysqld_safe log-error \"\/dbases\/master-a\/mysqld.log<\/span>\"\r\nerrlogfile=\"\/dbases\/master-a\/mysqld.log<\/span>\"\r\nget_mysql_option mysqld_safe pid-file \"\/dbases\/master-a\/mysqld.pid<\/span>\"\r\nmypidfile=\"\/dbases\/master-a\/mysqld.pid<\/span>\"\r\n\r\ndefaultfile=\"\/etc\/master-a.cnf\"<\/span>\r\n\r\nstart(){\r\n        touch \"$errlogfile\"\r\n        chown mysql:mysql \"$errlogfile\"\r\n        chmod 0640 \"$errlogfile\"\r\n        [ -x \/sbin\/restorecon ] && \/sbin\/restorecon \"$errlogfile\"\r\n        if [ ! -d \"$datadir\/mysql\" ] ; then\r\n            action $\"Initializing MySQL database: \" \/usr\/bin\/mysql_install_db --datadir=\"$datadir\" --user=mysql\r\n            ret=$?\r\n            chown -R mysql:mysql \"$datadir\"\r\n            if [ $ret -ne 0 ] ; then\r\n                return $ret\r\n            fi\r\n        fi\r\n        chown mysql:mysql \"$datadir\"\r\n        chmod 0755 \"$datadir\"\r\n        # Pass all the options determined above, to ensure consistent behavior.\r\n        # In many cases mysqld_safe would arrive at the same conclusions anyway\r\n        # but we need to be sure.\r\n        \/usr\/bin\/mysqld_safe  --defaults-file=\"$defaultfile\"<\/span> --datadir=\"$datadir\" --socket=\"$socketfile\" \\\r\n                --log-error=\"$errlogfile\" --pid-file=\"$mypidfile\" \\\r\n                --user=mysql >\/dev\/null 2>&1 &\r\n        ret=$?\r\n        # Spin for a maximum of N seconds waiting for the server to come up.\r\n        # Rather than assuming we know a valid username, accept an \"access\r\n        # denied\" response as meaning the server is functioning.        \r\n        if [ $ret -eq 0 ]; then\r\n      \u00a0     STARTTIMEOUT=30\r\n            while [ $STARTTIMEOUT -gt 0 ]; do\r\n\u00a0               RESPONSE=\/usr\/bin\/mysqladmin --socket=\"$socketfile\" --user=UNKNOWN_MYSQL_USER ping 2>&1<\/code> && break\r\n                echo \"$RESPONSE\" | grep -q \"Access denied for user\" && break\r\n                sleep 1\r\n                let STARTTIMEOUT=${STARTTIMEOUT}-1\r\n            done\r\n            if [ $STARTTIMEOUT -eq 0 ]; then\r\n                    echo \"Timeout error occurred trying to start MySQL Daemon.\"\r\n                    action $\"Starting $prog: \" \/bin\/false\r\n                    ret=1\r\n            else\r\n                    action $\"Starting $prog: \" \/bin\/true\r\n            fi\r\n        else\r\n            action $\"Starting $prog: \" \/bin\/false\r\n        fi\r\n        [ $ret -eq 0 ] && touch \/dbases\/master-a\/mysqld<\/span>\r\n        return $ret\r\n}\r\n\r\nstop(){ \r\n        MYSQLPID=cat \"$mypidfile\"  2>\/dev\/null <\/code>\r\n        if [ -n \"$MYSQLPID\" ]; then\r\n            \/bin\/kill \"$MYSQLPID\" >\/dev\/null 2>&1\r\n            ret=$?\r\n            if [ $ret -eq 0 ]; then\r\n                STOPTIMEOUT=60\r\n                while [ $STOPTIMEOUT -gt 0 ]; do\r\n                    \/bin\/kill -0 \"$MYSQLPID\" >\/dev\/null 2>&1 || break\r\n                    sleep 1\r\n                    let STOPTIMEOUT=${STOPTIMEOUT}-1\r\n                done\r\n                if [ $STOPTIMEOUT -eq 0 ]; then\r\n                    echo \"Timeout error occurred trying to stop MySQL Daemon.\"\r\n                    ret=1\r\n                    action $\"Stopping $prog: \" \/bin\/false\r\n                else\r\n                    rm -f \/dbases\/master-a\/mysqld<\/span>\r\n                    rm -f \"$socketfile\"\r\n                    action $\"Stopping $prog: \" \/bin\/true\r\n                fi\r\n            else\r\n                action $\"Stopping $prog: \" \/bin\/false\r\n            fi\r\n        else\r\n            ret=1\r\n            action $\"Stopping $prog: \" \/bin\/false\r\n        fi\r\n        return $ret\r\n}\r\n\r\nrestart(){\r\n    stop\r\n    start\r\n}\r\n\r\ncondrestart(){\r\n    [ -e \/dbases\/master-a\/mysqld<\/span> ] && restart || :\r\n}\r\n\r\n# See how we were called.\r\ncase \"$1\" in\r\n  start)\r\n    start\r\n    ;;\r\n  stop)\r\n    stop\r\n    ;;\r\n  status)\r\n    status mysqld\r\n    ;;\r\n  restart)\r\n    restart\r\n    ;;\r\n  condrestart)\r\n    condrestart\r\n    ;;\r\n  *)\r\n    echo $\"Usage: $0 {start|stop|status|condrestart|restart}\"\r\n    exit 1\r\nesac\r\n\r\nexit $?<\/pre>\n<\/div>\n
Step 7. Start each MySQL instance.<\/h4>\n
Now you can start each instance with the handy service<\/code> command.<\/p>\n
service mysqld-master-a start<\/pre>\n
Step 8. Connect to MySQL instances.<\/h4>\n
Now, to connect to each MySQL instance, you’ll need to specify the port and\/or socket file.<\/p>\n
mysql -P3307 --socket=\"\/dbases\/mysql-master-a\/mysql.sock\"<\/pre>\n","protected":false},"excerpt":{"rendered":"
I’m trying this new idea for backing up our production MySQL servers. I have a backup server that basically runs rdiff-backup in the morning across several servers, but then does nothing for the rest of the day. It’s a pretty decent machine, so I’d like to utilize some resources. Replicating a MySQL server is a … Continue reading Making Multiple MySQL Instances on One Server<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":1328,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[243,167],"tags":[242,63,179],"class_list":["post-1275","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-howto","category-technical","tag-database-admin","tag-mysql","tag-servers"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/mossiso.com\/wp-content\/uploads\/2012\/07\/Conrad_von_Soest_Brillenapostel-seeing-double.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9wosP-kz","_links":{"self":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/posts\/1275"}],"collection":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/comments?post=1275"}],"version-history":[{"count":20,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/posts\/1275\/revisions"}],"predecessor-version":[{"id":1644,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/posts\/1275\/revisions\/1644"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/media\/1328"}],"wp:attachment":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/media?parent=1275"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/categories?post=1275"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/tags?post=1275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}