Difference between revisions of "MySQL notes"
m (→mysqldump) |
|||
Line 185: | Line 185: | ||
== mysqldump == | == mysqldump == | ||
− | Dump | + | Dump specified tables from '''my_database''' database. |
<pre> | <pre> | ||
− | mysqldump - | + | mysqldump --host=prod-database.example.com --user=my_username --password="mypassword" my_database \ |
+ | users users_app_roles app_filenames app_dataset app_categories \ | ||
+ | > my_database-dump.sql | ||
</pre> | </pre> | ||
Revision as of 16:15, 8 October 2015
MySQL wouldn't be my first choice of database, but it isn't a bad choice either.
Contents
- 1 Documentation
- 2 ~/.my.cnf
- 3 Pretty output
- 4 Create a user
- 5 output without formatting (turn off ASCII art tables)
- 6 Error mixing old MySQL client with newer MySQL server
- 7 create root user directly
- 8 lost root password
- 9 show process info on server
- 10 show server status
- 11 show run-time server config
- 12 select into file
- 13 load table from file
- 14 mysqldump
- 15 dump shared memory files
- 16 I hate MySQL client
- 17 and don't do this...
- 18 clips
Documentation
~/.my.cnf
This is the RC file for mysql client applications. This file should be set with permissions 600 especially if you put your password in this file which is already a questionable idea.
chmod 600 ~/.my.cnf
The [client] section is read by all client apps including `mysql`, `mysqldump`, etc. The [mysql] section is read only by the `mysql` CLI. You could also skip the [client] section and put user, password, and host under [mysql] section.
[client] user = noah password = ze_zecret_passwort host = localhost [mysql] database = my_favorite_database auto-rehash=true prompt="\\R:\\m:\\s[\d] mysql> " safe-updates
Build MySQL for embedded
configure --with-comment="noah" \ --with-embedded-server \ --with-ssl \ --with-plugin-innobase \ --with-plugin=innobase,myisam \ --without-docs \ --without-man
Environment variables
You can also store your host and password in environment variables. This is stupid. For no good reason you cannot specify username and database name. It will use $USER by default, but if your mysql username is different than your login name then this is useless. But no matter, this is trivia anyway. You should not user MYSQL_PWD under normal circumstances.
export MYSQL_HOST=localhost export MYSQL_PWD=ze_zecret_passwort
Pretty output
Put \G on the end of a query for pretty output. The \G replaces the ;
Create a user
This creates a new user with full root privileges named USERNAME with no password.
$ mysql -u root -e "GRANT ALL ON *.* to USERNAME@localhost;" $ mysql -u root -e "GRANT ALL ON *.* to USERNAME@'%';"
This creates a new user full root privileges named USERNAME with the given password.
$ mysql -u root -e "GRANT ALL ON *.* to USERNAME@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION;" $ mysql -u root -e "GRANT ALL ON *.* to USERNAME@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;"
output without formatting (turn off ASCII art tables)
Often you want to get results from the mysql client without all the ASCII box table formatting and column names. The documentation for the '-e' option lies. It claims that '-e' will "Execute command and quit.(Output like with --batch)", but the output is not like with '--batch'. If output is to stdout it will still be formatted with ASCII boxes. The "-B" or "--batch" option will force the output to be TAB delimited no matter where the output is going. The "-N" option will turn off column names in the output. The following outputs the result of the query in TAB delimited format without column names:
mysql -B -N -e "select distinct table_schema from information_schema.tables"
If you pipe or redirect output mysql CLI will automatically use TAB delimited format, but it still outputw column names.
Error mixing old MySQL client with newer MySQL server
If you are using an old MySQL client to connect to a later version of MySQL you may get an error:
Client does not support authentication protocol requested by server; consider upgrading MySQL client
This happens when using a MySQL client prior to version 4.1 with a server later than version 4.1. The best thing to do is to upgrade the client. If that cannot be done then set the password on the server to use the old password hash:
SET PASSWORD FOR 'USERNAME'@'some_host' = OLD_PASSWORD('some_pass');
create root user directly
Some automatic installations will install without creating a root user. If you start mysqld with 'skip-grant-tables' then it won't allow you to create or update a user. You can still create the root user directly by inserting into the mysql.user table. This will create a root user and set the password to "password". After running this INSERT statement you should restart mysqld.
INSERT INTO `user` VALUES ('localhost','root','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0), ('%','root','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
lost root password
This is how you can reset your root password if it is lost (you cannot recover the password).
Stop mysql server.
/etc/init.d/mysql stop
Edit /etc/my.cnf or /etc/mysql/my.cnf and add this line anywhere after the [mysqld] section:
skip-grant-tables
Start mysql server:
/etc/init.d/mysql start
Now you can connect without a password. Grant commands won't work. You can reset the password by directly updating the User table:
mysql> use mysql mysql> update mysql.user set Password=password('newpassword') where User='root';
Remove skip-grant-tables from /etc/my.cnf.
Restart mysql server:
/etc/init.d/mysql restart
show process info on server
See what is locking and blocking other sessions on the server.
show full processlist\G
show server status
This will show server status:
show status;
You can reset many of the counters like this:
flush status;
show run-time server config
This shows the state of the server as currently running:
show variables;
You can filter:
show variables like 'log%';
You can also set some run-time variables.
set global foo = bar;
select into file
I always forget this:
SELECT * FROM foo INTO OUTFILE '/tmp/foo.sql';
select into CSV file
SELECT * FROM foo INTO OUTFILE '/tmp/foo.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load table from file
LOAD DATA INFILE '/tmp/foo.csv' INTO TABLE foo FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
mysqldump
Dump specified tables from my_database database.
mysqldump --host=prod-database.example.com --user=my_username --password="mypassword" my_database \ users users_app_roles app_filenames app_dataset app_categories \ > my_database-dump.sql
It is pretty easy to read directly from MySQL memmapped files. This can be handy when you need to hack a search through data files rather than doing a proper query. Sometimes it's faster to dump data this way.
#!/usr/bin/env python import mmap import os, sys, string def hex_filter (b, offset, block_size = 8): """Any unprintable characters are converted to line feeds, but only one line feed is printed per cluster of unprintable characters. """ unprintable_flag = True # used to track unprintable chunk state. for i in xrange (offset, offset+block_size): #if b[i] in string.printable: if ord(b[i])>=ord(' ') and ord(b[i])<=ord('~'): sys.stdout.write("%s"%b[i]) unprintable_flag = False else: if not unprintable_flag: sys.stdout.write("\n") unprintable_flag = True def main (): filename="/var/lib/mysql/var/test/rmscrub.MYI" file = open(filename, "rb") size = os.path.getsize(filename) data = mmap.mmap(file.fileno(), size, access=mmap.ACCESS_READ) print "Note that 'data' is a memmapped file and 'size' is taken from the memmapped file size," print "so len(data) and size should be the same:" print "len(data):", len(data) print "size: ", size block_size = 2048 size = 1000000 for i in xrange(0,size, block_size): hex_filter(data,i, block_size) main()
I hate MySQL client
OK, it's better than Oracle SQL*Plus, but that isn't saying much. One thing that really annoys me are the \ commands. I got burned by this in the worst possible way. I had a database name with a dot in it, "wikidb.old". I can't remember how it got there, but it was old and I wanted to drop it.
mysql> drop database wikidb.old; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.old' at line 1
Oh yeah, so the dot is used as a namespace separator in SQL, so it was getting confused by the ".old" part of the name. So that's easy. You just gotta escape the dot, right? Well everything uses backslash to escape special characters, right?
mysql> drop database wikidb\.old; ERROR: Usage: \. <filename> | source <filename> -> -> -> ; Query OK, 33 rows affected (0.24 sec)
I got both an "ERROR" and a "Query OK". Uhhh... what just happened to me? Oh, it just screwed me! It thought "\." means to source a file name ".old", hence the "ERROR", but for reasons I still don't understand it decided to give me the syntax error and it tried interpreting the statement in a completely different way. You see, "\" is an alternative way to end a statement. It works just like ";", so it read "drop database wikidb\" as a single statement and executed it. Terrific! Of course, I did have a database named "wikidb" on that server... I have never seen a system try to resolve an ambiguous syntax error by actually trying other interpretations. It didn't know if I meant "\" or "\.", so when one gave a syntax error it tried executing the other interpretation.
and don't do this...
Don't do this:
sudo mysql -N -e "select distinct concat('drop ', table_schema,';') from information_schema.tables" | xargs -r -i echo mysql -e "{}"
or this:
sudo ps axwwo cmd | grep -i mysqld | grep -m1 -E -o datadir=[^[:space:]]* | sed -e "s/datadir=\(.*\)/\\1/" | xargs -r -i echo rm -rf "{}"
OK, I'm not that mean -- see? I put an echo in the xargs statement to show what would have happened if someone was foolish enough to enter these commands.
clips
SHOW CREATE TABLE `some_table`; SHOW INDEX FROM `some_table`; -- Select only in the last month. SELECT session_date, session_id, username FROM some_table WHERE session_date>(now() - interval 31 day); -- Copy missing fields from one table to another. UPDATE a, b SET a.session_date=b.session_date WHERE a.session_id=b.session_id AND a.session_date IS NULL;