{"id":665,"date":"2009-11-10T15:56:04","date_gmt":"2009-11-10T23:56:04","guid":{"rendered":"http:\/\/www.codebelay.com\/blog\/?p=665"},"modified":"2009-11-10T15:58:55","modified_gmt":"2009-11-10T23:58:55","slug":"notes-on-adding-more-mysql-databases","status":"publish","type":"post","link":"https:\/\/www.codebelay.com\/blog\/2009\/11\/10\/notes-on-adding-more-mysql-databases\/","title":{"rendered":"Notes on adding more MySQL databases"},"content":{"rendered":"<p>Just notes for myself on adding more MySQL databases without shutting down the master database.<\/p>\n<p>on existing slave:<\/p>\n<div style=\"border: 1px solid #fff; background: #000; color: #fff; padding: 5px 5px 5px 5px;\">\n\/etc\/init.d\/mysqld stop\n<\/div>\n<p>copy data dir from \/var\/lib\/mysql and data from \/var\/run\/mysqld to new slave database:<\/p>\n<div style=\"border: 1px solid #fff; background: #000; color: #fff; padding: 5px 5px 5px 5px;\">\ncd \/var\/lib<br \/>\ntar cvf Mysql_slave.tar mysql\/*<br \/>\nscp Mysql_slave.tar root@new-db.com:\/var\/lib\/.<br \/>\ncd \/var\/run<br \/>\ntar cvf Mysqld_slave.tar mysqld\/*<br \/>\nscp Mysqld_slave.tar mysqld\/*<br \/>\nscp Mysqld_slave.tar root@new-db.com:\/var\/run\/.\n<\/div>\n<p>copy \/etc\/my.cnf from old slave to new slave<br \/>\nadd entry for new server-id<\/p>\n<p>start existing slave:<\/p>\n<div style=\"border: 1px solid #fff; background: #000; color: #fff; padding: 5px 5px 5px 5px;\">\ncd \/var\/lib<br \/>\ntar xvf Mysql_slave.tar<br \/>\ncd \/var\/run<br \/>\ntar xvf Mysqld_slave.tar<br \/>\n\/etc\/init.d\/mysqld start\n<\/div>\n<p>start new slave:<\/p>\n<div style=\"border: 1px solid #fff; background: #000; color: #fff; padding: 5px 5px 5px 5px;\">\n\/etc\/init.d\/mysqld start<br \/>\nmysql<br \/>\nstart slave;\n<\/div>\n<p>on masterdb:<br \/>\ne.g.:<\/p>\n<div style=\"border: 1px solid #fff; background: #000; color: #fff; padding: 5px 5px 5px 5px;\">\ngrant replication slave on *.* to &#8216;repl&#8217;@&#8217;192.168.107.33&#8217; identified by &#8216;password&#8217;;\n<\/div>\n<p>test on master:<br \/>\ncreate database repl;<\/p>\n<p>check on slave:<br \/>\nshow databases; \/* should show new database *\/<\/p>\n<p>test on master:<br \/>\ndrop database repl;<\/p>\n<p>check on slave:<br \/>\nshow databases; \/* new database should be dropped *\/<\/p>\n<p>Now it&#8217;s time to turn this into an automated shell script with <a href=\"http:\/\/expect.nist.gov\/\">Expect<\/a> in there.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Just notes for myself on adding more MySQL databases without shutting down the master database. on existing slave: \/etc\/init.d\/mysqld stop copy data dir from \/var\/lib\/mysql and data from \/var\/run\/mysqld to new slave database: cd \/var\/lib tar cvf Mysql_slave.tar mysql\/* scp Mysql_slave.tar root@new-db.com:\/var\/lib\/. cd \/var\/run tar cvf Mysqld_slave.tar mysqld\/* scp Mysqld_slave.tar mysqld\/* scp Mysqld_slave.tar root@new-db.com:\/var\/run\/. copy [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[312,315,288,33],"tags":[],"class_list":["post-665","post","type-post","status-publish","format-standard","hentry","category-databases","category-mysql-databases","category-scalability-hacking","category-webapps"],"_links":{"self":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/posts\/665","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/comments?post=665"}],"version-history":[{"count":0,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/posts\/665\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/media?parent=665"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/categories?post=665"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/tags?post=665"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}