Export to GitHub

mysql-replace-tables-live - Documentation.wiki

Usage

``` /* usage: mysql_replace_tables_live.py [options] file.frm file.MYD file.MYI [ file.frm file.MYD file.MYI ] ... (file triplets can be passed in any order)

options: -h, --help show this help message and exit -d DATABASE, --database=DATABASE MySQL database that contains the tables to be replaced -u USER, --user=USER MySQL connection username -p PASSWORD, --password=PASSWORD MySQL connection password -q, --quiet Bypass the normal confirmation step and just do the deed. */ ```

To see this in action you need to have some table files to work with. To demonstrate I will use the the super-smack data, since it will be familiar to anyone who does MySQL hacking of this level.

Demonstration

  • I'm using the select example from the super-smack tutorial.

  • Getting super-smack installed and configuring it's sample file takes a little work. If you do not wish to use it, that's fine. This demonstration is still equally valid.

    1. The first time you run the example from the super-smack tutorial, it creates the table we will work with. /* $ super-smack -d mysql select-key-mysql1-localhost.smack 10 1000 Error running query select count(*) from http_auth:Table 'test.http_auth' doesn't exist Creating table 'http_auth' Loading data from file '/var/smack-data/words.dat' into table 'http_auth' Table http_auth is now ready for the test Query Barrel Report for client smacker1 connect: max=8ms min=5ms avg= 6ms from 10 clients Query_type num_queries max_time min_time q_per_s select_index 20000 2 0 4259.72 */
    2. Here you can see the table files that were created. /* $ ls -la /opt/local/var/db/mysql5/test/http_auth.* -rw-rw---- 1 mysql admin 5310000 Oct 8 11:20 /opt/local/var/db/mysql5/test/http_auth.MYD -rw-rw---- 1 mysql admin 2949120 Oct 8 11:20 /opt/local/var/db/mysql5/test/http_auth.MYI -rw-rw---- 1 mysql admin 8654 Oct 8 11:20 /opt/local/var/db/mysql5/test/http_auth.frm */
    3. Now, I will alter the table to give us something to use as our replacement files later. ``` /*

$ mysql -uroot test -vv -e 'alter table http_auth AUTO_INCREMENT=99999999, add column id int NOT NULL AUTO_INCREMENT, add key (id);'

alter table http_auth AUTO_INCREMENT=99999999, add column id int NOT NULL AUTO_INCREMENT, add key (id)

Query OK, 90000 rows affected (0.99 sec) Records: 90000 Duplicates: 0 Warnings: 0

Bye / 1. Here you can see that all the files are now larger. /$ ls -la /opt/local/var/db/mysql5/test/http_auth.* -rw-rw---- 1 mysql admin 5670000 Oct 8 11:21 /opt/local/var/db/mysql5/test/http_auth.MYD -rw-rw---- 1 mysql admin 3873792 Oct 8 11:21 /opt/local/var/db/mysql5/test/http_auth.MYI -rw-rw---- 1 mysql admin 8680 Oct 8 11:21 /opt/local/var/db/mysql5/test/http_auth.frm / 1. I copy the files to another directory, and drop the tables from mysql / $ cp /opt/local/var/db/mysql5/test/http_auth.* /tmp/

$ mysql -uroot test -vv -e 'drop table http_auth;'

drop table http_auth

Query OK, 0 rows affected (0.01 sec)

Bye $ ls -la /opt/local/var/db/mysql5/test/http_auth.* ls: /opt/local/var/db/mysql5/test/http_auth.*: No such file or directory / 1. Running super-smack again, recreates the original table. / $ super-smack -d mysql select-key-mysql1-localhost.smack 10 1000 |expand -t 16 Error running query select count() from http_auth:Table 'test.http_auth' doesn't exist Creating table 'http_auth' Loading data from file '/var/smack-data/words.dat' into table 'http_auth' Table http_auth is now ready for the test Query Barrel Report for client smacker1 connect: max=9ms min=8ms avg= 8ms from 10 clients Query_type num_queries max_time min_time q_per_s select_index 20000 2 0 3804.22 $ ls -la /opt/local/var/db/mysql5/test/http_auth. -rw-rw---- 1 mysql admin 5310000 Oct 8 11:32 /opt/local/var/db/mysql5/test/http_auth.MYD -rw-rw---- 1 mysql admin 2949120 Oct 8 11:32 /opt/local/var/db/mysql5/test/http_auth.MYI -rw-rw---- 1 mysql admin 8654 Oct 8 11:32 /opt/local/var/db/mysql5/test/http_auth.frm / 1. Now that the stage is set, I will run mysql-replace-tables.py / $ mysql_replace_tables_live.py usage: mysql_replace_tables_live.py [options] file.frm file.MYD file.MYI [ file.frm file.MYD file.MYI ] ... (file triplets can be passed in any order)

options: -h, --help show this help message and exit -d DATABASE, --database=DATABASE MySQL database that contains the tables to be replaced -u USER, --user=USER MySQL connection username -p PASSWORD, --password=PASSWORD MySQL connection password -q, --quiet Bypass the normal confirmation step and just do the deed. $ mysql_replace_tables_live.py -d test -u root /tmp/http_auth.* mysql-replace-tables-live 1. The MySQL connection has been made using username: root 2. The MyISAM table files were passed for: http_auth 3. The table above will be WRITE LOCKed. 4. FLUSH TABLES will be called to flush any caches writes to disk. 5. The table files will be replaced with the files you passed, which are: /tmp/http_auth.MYD /tmp/http_auth.MYI /tmp/http_auth.frm 6. The schema path was deduced by querying the MySQL server, it is: /opt/local/var/db/mysql5/test 7. FLUSH TABLES will be called again, because it also includes RESET CACHE. 8. The lock will then be released, and the connection closed.

Do wish to proceed (y/[n])? $ / * Here are a few things to notice: 1. Calling the script with no arguments displays usage 1. Unless you explicitly specify quiet mode, you will get detailed verification of the steps to be taken, and an opportunity to bail out (which is the default). 1. Now I can demonstrate calling super-smack as a background process and doing the replace after a 1 second delay / $ super-smack -d mysql select-key-mysql1-localhost.smack 10 1000 & \

sleep 1; \ sudo mysql_replace_tables_live.py -q -d test -u root /tmp/http_auth.*
[1] 21591

LOCK TABLES http_auth WRITE; FLUSH TABLES copying... /tmp/http_auth.MYD -> /opt/local/var/db/mysql5/test/http_auth.MYD /tmp/http_auth.MYI -> /opt/local/var/db/mysql5/test/http_auth.MYI /tmp/http_auth.frm -> /opt/local/var/db/mysql5/test/http_auth.frm FLUSH TABLES UNLOCK TABLES

Query Barrel Report for client smacker1 connect: max=9ms min=5ms avg= 7ms from 10 clients Query_type num_queries max_time min_time q_per_s select_index 20000 1 0 2947.83

[1]+ Done super-smack -d mysql select-key-mysql1-localhost.smack 10 1000 $ */ ``` * Here are a few things to notice: 1. For you non-unixheads: * The & at near the end of the super-smack command causes it to run in the background. * The \ at the end of each line simply allow me to use line wrapping for readability. * The > at the beginning of the wrapped lines indicates that I'm on a wrapped line, not a new line. * sleep is a command that waits N seconds before continuing on to the next command. 1. The -q option tells mysql_replace_tables_live.py to suppress the verification step. 1. The first block of response text comes from mysql_replace_tables_live.py and is a mixture of the SQL commands sent to the server and file copy progress. 1. The second block of response text comes from super-smack upon job completion and indicates the no significant impact was made on performance. 1. If super-smack receives any errors, it will notify you of them. 1. super-smack issue 20,000 queries on the table at a rate of 2,947.83 per second. 1. The table files where replace live in the middle of the stream of queries. 1. Not a single query failed!