{"id":2324,"date":"2015-11-04T11:59:09","date_gmt":"2015-11-04T11:59:09","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2324"},"modified":"2024-01-27T17:17:19","modified_gmt":"2024-01-27T17:17:19","slug":"setting-up-second-mysql-instance-replication-on-linux-in-10-steps","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/setting-up-second-mysql-instance-replication-on-linux-in-10-steps","title":{"rendered":"Setting-up second MySQL instance &#038; replication on Linux in 10 steps"},"content":{"rendered":"\n<p>This post will help you configure second MySQL instance on same machine and also setup replication between them. We often see requirement for having multiple MySQL instances on same machine for restore, testing or backup purposes.<br><\/p>\n\n\n\n<p>This is a quick setup guide of 10-steps to install and configure MySQL instance on 3307 port, and make it slave from MySQL running on port 3306.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>This post assumes you already have a MySQL instance running on port 3306.<\/p>\n\n\n\n<p>1. Make datadir, tmpdir and correct permissions:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> mkdir -p \/var\/lib\/mysql3307\/tmp\/\n chown -R mysql:mysql \/var\/lib\/mysql3307\n<\/pre>\n\n\n\n<p>2. Create config<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> cp \/etc\/my.cnf \/etc\/my3307.cnf (Correct relevant parameters)\n# Usually we will set following parameters:\n\n    datadir=\/var\/lib\/mysql3307\n    socket=\/var\/run\/mysql\/mysqld3307.sock\n    server-id=&lt;different-than-master&gt;\n    port=3307\n    pid-file=\/var\/lib\/mysql3307\/mysqld3307.pid\n    log-error=\/var\/lib\/mysql3307\/mysqld3307.err\n    tmpdir=\/var\/lib\/mysql3307\/tmp\/\n<\/pre>\n\n\n\n<p>3. Install basic auth system \/ dbs:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql_install_db --user=mysql --basedir=\/usr --datadir=\/var\/lib\/mysql3307 --defaults-file=\/etc\/my3307.cnf\n<\/pre>\n\n\n\n<p>4. Start mysql<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> mysqld_safe --defaults-file=\/etc\/my3307.cnf --user=mysql &amp;\n<\/pre>\n\n\n\n<p>5. Take fulldump from 3306 instance:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> mysqldump -uroot -p --all-databases --master-data=2 | gzip &gt; backup.sql.gz\n<\/pre>\n\n\n\n<p>6. Load it to new 3307 instance<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> zcat backup.sql.gz  | MySQL -uroot -p --port=3307 --socket=\/var\/run\/mysql\/mysqld3307.sock\n<\/pre>\n\n\n\n<p>7. Grab binary log co-ordinates from backup:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> zcat backup.sql.gz | head -30 | grep -i change\n<\/pre>\n\n\n\n<p>8. Create replication user on master (3306 MySQL instance):<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> grant replication slave on *.* to 'repluser'@'%' identified by 'replpass';\n<\/pre>\n\n\n\n<p>9. Run change master to on 3307 slave using binlogs take from step #7:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">#You may connect to MySQL as:\n$ mysql  -uUSER -p --socket=\/path\/to\/3307\/socket\n\n\nmysql&gt;CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_PORT=3306, MASTER_LOG_FILE='XXXX', MASTER_LOG_POS=XXXX;\n<\/pre>\n\n\n\n<p>10. Start slave on 3307.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">start slave;<\/pre>\n\n\n\n<p>Hope this helps.<\/p>\n","protected":false},"excerpt":{"rendered":"This is a quick setup guide of 10-steps to install and configure (multiple) MySQL instance on 3307 port, and make it slave from MySQL running on port 3306.\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[858,862,859,861,863,860],"class_list":{"0":"post-2324","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"tag-how-to-setup-mysql","9":"tag-install-2-mysql-on-linux","10":"tag-multiple-mysql-instances","11":"tag-mysqld_multi","12":"tag-step-by-step-second-mysql-instance-configuration-on-same-machine","13":"tag-two-mysql-instances"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2324","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2324"}],"version-history":[{"count":8,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2324\/revisions"}],"predecessor-version":[{"id":3196,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2324\/revisions\/3196"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2324"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2324"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2324"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}