Mysql – How to regularly export from one database to another (on same server) in a daily fashion

apache-2.2MySQLPHP

Running a beta site (different platform than live site).

Already have export / import process via php script. Automatically gets data from one database and exports to another.

I want to automatically run this (at least daily) transfer to update the beta site with content from live site.

What's my simplest option (aside from manually running the script)?

I was considering:

Writing additional functionality into the script to track when last run or run based on a set server timestamp
Writing additional functionality to check database for new / updated data (no idea if this is possible)
?????

Also, perhaps this is better suited for a different site (not serverfault), let me know.

Best Answer

You should be able to accomplish this with a simple cronjob that does a sqldump from the first, then sqlimport and restart the second SQL instance (if necessary...)

Personally, I like adding 'logger' statements to my cronjobs to note a step that's beginning and another when it ends so that I can track how long things take.

Related Solutions

MySQL – Export and Import Privileges to a New Server

Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the "SHOW GRANTS FOR" command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

The first mysql command uses SQL to generate valid SQL which is piped to the second mysql command. The output is then piped through sed to add pretty comments.

The $@ in the command will allow you to call it as: mygrants --host=prod-db1 --user=admin --password=secret

You can use your full unix tool kit on this like so:

mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret

That is THE right way to move users. Your MySQL ACL is modified with pure SQL.

MySQL Configuration – How to Bind MySQL Server to Multiple IP Addresses

No, there isn't (I just checked 1 hour ago). You can comment the bind-address in my.cnf:

Note: « 1 hour ago » is now more than 10 years ago.

#skip-networking
#bind-address                   = 127.0.0.1

If you want only 2 IPs, you will then have to use a firewall.

For MySql version 8.0.13 and above, you can specify a list of comma-separated IP addresses.

bind-address = 10.0.0.1,10.0.1.1,10.0.2.1

Relevant MySql documentation.

Remember to restart your MySQL instance after changing the config file.

Best Answer

Related Solutions

MySQL – Export and Import Privileges to a New Server

MySQL Configuration – How to Bind MySQL Server to Multiple IP Addresses

Related Topic