Thesql: how to copy database and required dependencies

MySQLmysqldump

I need to set up an automated copy of a single mysql database (not the whole DBMS). If I use mysqldump to dump and recreate a DB, the copy may be unusable because the user accounts need to also be copied. Triggers in the source DB for instance may be copied, but there's no guarantee that they will execute correctly in the new DB because the original triggers were designed to run as some particular user.

A couple of questions:

how can I automate copying of the user accounts and permissions?
is there a way, when copying a db to check whether all of its dependencies are met? (E.g. are all of the databases referred to by the stored procedures present?)

I understand that I can replicate the whole DBMS – this is overkill (and often not practical for hourly regressions)

Best Answer

Users and grants are stored in the mysql database, so you could insert rows directly. MySQL Workbench includes the mysqluserclone utility, which provides a safer way to copy user accounts and permissions from one server to another.

I don't know of any direct way to check dependencies, but INFORMATION_SCHEMA contains all the necessary metadata. Other utilities from MySQL Workbench such as mysqldiff and mysqldbcompare would probably help in determining when something is missing.

Related Solutions

Mysql – How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server

Before you give up on mysqldump, have you ever considered performing parallel mysqldumps?

I wrote a script on how to do this: How can I optimize a mysqldump of a large database?

Going back to your question, you could resort to doing rsync of a live server as long as the target DB server will have the exact same version of MySQL that the source DB server has.

Just run rsync against /var/lib/mysql on a master and copy it to /var/lib/mysql on the target server. Of course, I would run rsync several times. Until the final rsync you should run the FLUSH TABLES WITH READ LOCK. Before copying make sure you hose all binary logs and start from scratch.

If you want the target server to have binary logging, please make you have something like the following in /etc/my.cnf:

[mysqld]
log-bin=mysql-bin

Please try running this script in the event you do not want to shutdown MySQL on the master:

RSYNCSTOTRY=10
cd /var/lib/mysql
X=0
while [ ${X} -lt ${RSYNCSTOTRY} ]
do
    X=`echo ${X}+1|bc`
    rsync -r * slaveserver:/var/lib/mysql/.
    sleep 60
done
mysql -u... -p... -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);"
sleep 60
SLEEPID=`mysql -u... -p... -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`
rsync -r * slaveserver:/var/lib/mysql/.
mysql -u... -p... -e"KILL ${SLEEPID};"

I am a little more conservative in terms of data and index pages being cached while doing this. Personally, I prefer to shutdown mysql after several rsyncs instead of the FLUSH TABLES WITH READ LOCK. Another alternative to this script would be the following script which shuts down mysql for the final rsync:

mysql -u... -p... -e"RESET MASTER;"
RSYNCSTOTRY=10
cd /var/lib/mysql
X=0
while [ ${X} -lt ${RSYNCSTOTRY} ]
do
    X=`echo ${X}+1|bc`
    rsync -r * slaveserver:/var/lib/mysql/.
    sleep 60
done
service mysql stop
rsync -r * slaveserver:/var/lib/mysql/.
service mysql start

Give it a try !!!

CAVEAT

If you have any InnoDB data, you should set this about 1 hour before attempt to rsync:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

This will cause InnoDB to page out uncommitted data from the InnoDB Buffer Pool faster. In MySQL 5.5, this is no longer necessary.

Mysql – Backup and restore “thesql” database

Most people that perform mysqldumps just use the --all-databases option. That will include the mysql database. There are two schools of thought as to whether one should include the mysql database.

Why not to include mysql ???

When you mysqldump the mysql schema, you should make yourself aware of differences in MySQL versions, particularly the mysql.user table.

In MySQL 5.0, mysql.user has 37 columns
In MySQL 5.1, mysql.user has 39 columns
In MySQL 5.5, mysql.user has 42 columns

Restoring a mysqldump from one version can result in certain privileges disappearing when restored :

Make sure you handle dumping user grants as a special script. There are two methods for this:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

I have discussed this before

Apr 09, 2012 : Mysql users deleted
Apr 12, 2012 : Cannot GRANT privileges as root
Jun 13, 2012 : Fastest way to move a database from one server to another

Why include mysql ???

The only case for which you can mysqldump the mysql schema is to restore it to the same version of mysql

UPDATE 2014-12-30 17:42 EST

My original answer covered just the MySQL Grants since this particular aspect of the mysql schema is the easiest to break and overlook.

There are other reasons not to just backup the mysql schema unless it is necessary

If you want to retain the timestamps for all Stored Procedures, you can mysqldump the mysql.proc table by itself and load it. You can also just copy the proc.frm, proc.MYD, and proc.MYI then reload it by copying it back into the mysql subsfolder and runninf FLUSH TABLES;
If you use table based logging for the general log (mysql.general_log) and slow log (mysql.slow_log), you do not want to backup these logs and move them to another server where the logs do not semantically apply. It is your choice if you want to restore them.
If you set any timezone info in the mysql, be very careful if you do not want to restore the mysql schema in a data center that is in another timezone.

If you want to be careful about what you want to backup and restore in the mysql schema, you should mysqldump the mysql schema in a separate file.

The number of tables in the mysql schema, as well as the number of columns, will vary from version to version. So, you should login to mysql and run

SHOW TABLES FROM mysql;

The list of tables may not include events (like MySQL 5.0), may not have InnoDB system tables (like MySQL 5.6 has), may not include tablespace grants (like MySQL 5.6), and so forth. Sp, if you feel you must backup the mysql schema, you be very conservative (ok very picky and careful) about what tables you truly need to backup from the mysql schema.