How-to-copy-the-invertory-database-to-a-local-machine.md

Context

To developing the application, we will constantly query the QA database running on the LOCTEST server.
The queries that are under testing are usually very complicated. The waiting time with the server ranges from seconds to 20 minutes or even longer.
The LOGTEST server may be down or gets extremely slower occasionally .
The database qa1_loctest_inventory is mostly used. The article here introduces how to copy the invertory database to a local Mac environment

The procedures

1. Dump file from LOCTEST

mysqldump -h ctsqavlt02.loctest.gov --user=[username] --password=[password] --max_allowed_packet=512M --databases qa1_loctest_inventory --skip-extended-insert > inventory.sql

Note: The [username] and [password] should be a superuser account described from Tong's email.

The resulting inventory.sql is about 35GB.

-rw-r--r--  1 admin  staff    35G Nov 22 12:59 inventory.sql

We could use tail to verify the dumped data.

admins-MacBook-Air:test_vagrants admin$ tail inventory.sql 

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-11-22 17:59:39

2. Restore the database to the local

2.1 First, make sure a copy of MySQL, mostly as as community version, has been successfully installed.

2.2 Edit the `my.cnf` file to change the settings for MySQL

Add one line

max_allowed_packet = 512M

The location of my.cnf is varying depending on OS. If it is not found, create it under the etc directory.

2.3 Restore the database

mysql -u [username] -p < inventory.sql

It may cost 2-4 hours to restore the database. The resulting database is about 38.60GB.

mysql> SELECT sum(round(((data_length + index_length) / 1024 / 1024 / 1024), 2)) as "Size in GB" FROM information_schema.TABLES WHERE table_schema = "qa1_loctest_inventory";
+------------+
| Size in GB |
+------------+
|      38.60 |
+------------+

Note: The restored database is slightly smaller than the original one at the LOCTEST server, since some long blob files have been ignored by the --skip-extended-insert option.

2.4 Test the restored database

We could use any SQL syntax from Tong's wiki page to test the availability of the new database.

For example -

select count(*) from (
select 
    bi.bag_key bag_key,
    bi.pkey bag_instance_key,
    fc.bag_instance_version_key, 
    fc.extension, 
    fc.file_count,
    bc.byte_count
from core_bag_instance_version_file_extension_file_count fc
inner join core_bag_instance bi on fc.bag_instance_version_key=bi.current_bag_instance_version_key
inner join core_bag_instance_version_file_extension_byte_count bc on fc.bag_instance_version_key=bc.bag_instance_version_key and fc.extension=bc.extension
) as a;

dapangmao/How-to-copy-the-invertory-database-to-a-local-machine.md