Answer to your first question: Each file contains info for the table
cars.frm
has the table structure
cars.MYD
has the table data
cars.MYI
has the table indexes
Given this layout, the cars table uses the storage engine MyISAM.
Answer to your second question: Yes, provided the following circumstances
- every table uses the storage engine MyISAM
- no INSERTs, UPDATEs, or DELETEs are running
- DB Connections using TCP/IP are disabled
To check to see if all the tables are MyISAM, run this query:
SELECT COUNT(1) TableCount,engine
FROM information_schema.tables
WHERE table_schema='db_test_1'
GROUP BY engine;
You should only see just MyISAM. If you see other storage engines, you cannot just copy.
If your root@localhost password is mypass
, here is all you need to to safely move all the tables regardless of storage engine:
# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 | mysql ${USERPASS} -A -Ddb_test_2
# service mysql restart
If the data is huge, do this instead:
# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 > /root/MyData.sql
# mysql ${USERPASS} -A -Ddb_test_2 < /root/MyData.sql
# service mysql restart
Give it a Try !!!
Using lock-tables=false becomes irrelevant given MyISAM's table locking approach to DML.
You asked
My guessing is all their backups are inconsistent, since they don't lock all the tables, but one by one with every dump. Am I correct?
Yes, you are correct. Furthermore, when it comes to XtraBackup and MySQL Enterprise Backup, there must be a FLUSH TABLES WITH READ LOCK
if MyISAM is present.
Oracle and I recently had dialogue. They asked me to change everything to InnoDB in order to use Enterprise Backup. When I explained I had 40GB and 2TB MyISAM (most of the tables had hundreds of partitions), they understood. I still that after all these years, you must still take matters into your own hands for point-in-time consistency with MyISAM.
SUGGESTION
Your best solution would be to use MySQL Replication and run the backups from the Slave
I have suggested this before
You may want to use binlog_format=ROW on Master and Slave to catch granular changes before running STOP SLAVE; FLUSH TABLES;
on the Slave. Then, you can mysqldump from the Slave without worrying about any Storage Engine locking or consistency issues.
Give it a Try !!!
Best Answer
This is based on answers from How do you mysqldump specific table(s)?
To exclude all tables from a mysqldump that starts with
foo_
, here is the shell script to do itTo exclude all tables from a mysqldump that starts with
foo_
, adjust this lineto whatever pattern you need. Maybe you can use the REGEXP operator
Give it a Try !!!