Home / server / Questions / 442608
Accepted
Mr.Boon
Asked: 2012-10-27 01:10:10 +0800 CST

Setting up MySQL database replication [without restarting mysql]

  • 772

I'm trying to setup MySQL db replication, it seems pretty straight forward.

I was using this tutorial: http://www.howtoforge.com/mysql_database_replication

Now I run a rather large MySQL database for a very large website, and in this tutorial it asks me to restart MySQL to apply the new settings in the /etc/my.cnf file.

I'm try to avoid that step at all costs, as I know that restarting MySQL can take a few minutes on my machine (due to large logs/dbs), and I don't want any downtime.

Is there a way to apply the necessary settings WITHOUT fully restarting Mysql?

mysql

2 Answers

  1. Best Answer

    Lots of settings in my.cnf can be applied from within MySQL with SET GLOBAL. For instance:

    mysql> SET GLOBAL log_slow_queries = On;

    But some are read-only from within MySQL; log-bin is one of these.

    mysql> set global log_bin = foo;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
    • 1

  2. You need to add new DB to existing DB or merge two existing DB servers? If you need to add new one, check that your server collecting binlogs on production server by command "show master status". You will need to set server ID on new server (it must be not 1, because 1 is default server ID and it used on your "old" server). Also check that your "old" server listen on real interfaces. If "old" server used only 127.0.0.1, you will need to restart it, because you can't change listen and ID params on running server without restart. All other replication params may be changed by "set global" command, but I strongly recommended to set it both in my.cnf to avoid problems when old server restarted.

    • 0