Entry

How can i make mysql send a copy of all data to a backup database in realtime?

Feb 16th, 2008 02:52
dman, Jason Ross, josh oshiro, http://sturly.com

depending what you're looking to accomplish, you may wish to enable 
replication. You can learn more about it from the mysql documentation 
at http://dev.mysql.com/doc/mysql/en/Replication.html
Here's a handy mini-howto i threw together one day.
------
On the master server:
Shutdown the mysql engine:
   /export/opt/mysql/bin/mysqladmin -u root -p shutdown
Edit /etc/my.cnf and ensure that in the mysqld section the log-bin
line is not commented and that the server-id line is set to 1
Example:
   ~[mysqld]
      log-bin
      server-id = 1
Connect to the mysql engine and run the following:
   GRANT REPLICATION SLAVE, SUPER, RELOAD, SELECT
   ON *.*
   TO ~ReplicationUser@~[slavehost.domain]
   IDENTIFIED BY '~SomePassword';
Where the ~[slavehost.domain] is the hostname and domain of the slave 
server.
Next, run the following:
   FLUSH TABLES WITH READ LOCK;
IN A SEPARATE SHELL do the following:
   cd /export/opt/mysql/data
   tar cvf /tmp/mysql-snap.tar ~[dbName]
Where ~[dbname] is the directory name, (or space separated list of 
names)of the databases you will be replicating.
NOTE: if you exit the mysql shell to do this, the READ LOCK will not be 
in effect any longer. You need to remain in the mysql shell and start a 
new command line session to make the tar snapshot.
In the mysql engine run
   SHOW MASTER STATUS;
The output will look similar to this:
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| masterhost-bin.00001 |       79 |              |                  |
+----------------------+----------+--------------+------------------+
Make note of the File, and Position, then run:
   UNLOCK TABLES;
On the slave server:
Shutdown the mysql engine:
   /export/opt/mysql/bin/mysqladmin -u root -p shutdown
Edit /etc/my.cnf and ensure that in the mysqld section the log-bin line 
IS commented (or not present at all) and that the server-idline is not 
commented and set to something other than 1
Example:
   ~[mysqld]
      #log-bin
      server-id = 2
Transfer the mysql-snap.tar file from the master host to the slave: 
   scp someuser@master_host:/tmp/mysql-snap.tar /tmp/
In the mysql data directory, untar the mysql snapshot:
   cd /export/opt/mysql/data
   tar xvf /tmp/mysql-snap.tar
Start the mysql server:
   /etc/init.d/mysql start 
Connect to the mysql engine and run the following:
   CHANGE MASTER TO
   MASTER_HOST='master_host_name',
   MASTER_USER='replication_user_name',
   MASTER_PASSWORD='replication_password',
   MASTER_LOG_FILE='recorded_log_file_name',
   MASTER_LOG_POS=recorded_log_position; 
Where master_host_name is the master host name, and the 
replication_user_name/replication_password are the username/password 
set up for replication on the master server. 
The recorded_log_file_name and recorded_log_position should be the 
values you obtained by running SHOW MASTER STATUS on the master host.
   EXAMPLE:
      CHANGE MASTER TO
      MASTER_HOST='masterhost.domain.com',
      MASTER_USER='~ReplicationUser',
      MASTER_PASSWORD='~SomePassword',
      MASTER_LOG_FILE='masterhost-bin.00001',
      MASTER_LOG_POS=79;
Next, run:
   START SLAVE;
That's all there is to it, in theory.