Sat, 01 Oct 2011
/Admin/databases/MySQL:
Cheat Sheet for the MySQL Command Line
phpmyadmin is a very handy GUI front-end for MySQL administration, but if
there is no web server installed on your MySQL server, it is non-trivial to
get phpmyadmin talking to it from a remote host. Especially if your remote
host is behind a dynamic IP. And of course, the command line is just plain
good for you. ;-) Here are some other cheat sheets for reference[1][2][3].
And here is mine:
Login to your mysql database on hostname as root (-h and -u are
optional, -p forces a password prompt):
mysql -h hostname -u root -p
Create a database:
mysql> create database [databasename];
Delete a database:
mysql> drop database [databasename];
List all databases:
mysql> show databases;
Switch to a database:
mysql> use [db name];
List the tables in the db:
mysql> show tables;
See a table's field formats:
mysql> describe [table name];
mysql> show columns from [table name];
Delete a db:
mysql> drop database [database name];
Delete a table:
mysql> drop table [table name];
Show data in a table:
mysql> SELECT * FROM [table name];
mysql> SELECT * FROM [table name] limit 10;
mysql> SELECT * FROM [table name] limit 100,10;
mysql> SELECT * FROM [table name] order by [col name] limit 10;
mysql> SELECT * FROM [table name] order by [col name] desc limit 10;
Counting the number of rows in a table:
mysql> SELECT COUNT(*) FROM pet;
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
Show rows where "field name" has the value "whatever":
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";
mysql> SELECT * FROM [table name] WHERE [field1] = "what1" AND [field2] = "what2";
Pattern Matching (% is wildcard for an arbitrary # of chars, _ is any single
character):
mysql> SELECT * FROM table WHERE rec LIKE "blah%";
mysql> SELECT * FROM table WHERE rec like "_____";
To insert records into a table:
INSERT into backup_run_log (start_time) VALUE (NOW());
To get the index of the most recently inserted row:
SELECT MAX(backup_run_log_id) FROM backup_run_log;
To change the contents of a table:
mysql> UPDATE table_name SET field1 = 'x',field2 = 'y',field3 = 'z' where fieldname = 'user';
mysql> UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");
mysql> UPDATE `mos2_content` SET introtext = replace(introtext,"{p}","") WHERE `title` REGEXP '-0';
To delete a row, for eg. to delete a user record from MySQL:
mysql> DELETE FROM mysql.user WHERE user='username' and host='localhost';
Column operations:
<![CDATA[
mysql> alter table [table name] drop column [column name];
mysql> alter table [table name] add column [new column name] varchar (20);
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.
mysql> alter table [table name] add unique ([column name]);
Make a column bigger.
mysql> alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.
mysql> alter table [table name] drop index [colmn name];
]]>
Dumping databases / tables to a (backup) file:
mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restoring a database from a dump:
mysql> create database databasename;
mysql> quit;
mysql -ppassword databasename < dump_databasename.sql
[1] http://www.tech-geeks.org/contrib/mdrone/mysql-stuff/mysql-cheatsheet.html
[2] http://www.pantz.org/software/mysql/mysqlcommands.html
[3] http://www.nparikh.org/unix/mysql.php
posted at: 09:50 | path: /Admin/databases/MySQL | permanent link to this entry
Mon, 13 Dec 2010
/Admin/databases/MySQL:
MySQL Performance Tuning
***Update: with the current state of my Apache and MySQL tuning efforts, the graphs of my server's
memory usage have gone from a state of consistently zero free memory and wild swings in swap usage and
swapping, to a fairly consistent state of some free memory, with the occasional spike that leads to
zero free memory and a LITTLE BIT of swap usage and swapping. (This with 256M of memory.) Things seem
much more stable, but only time will tell if I have completely solved the problem of the machine
sometimes swapping itself to death.
This is a continuation of my related post on Apache performance tuning.
I found a very informative little tool call "mysqltuner" in the Debian archives. Upon running, it
gives a nice concise breakdown of what is going on with my MySQL server performance-wise, followed
by some suggestions for how to increase performance.
I found out, for instance, that the default values for interactive_timeout and wait_timeout were
set to (what seems like an outrageous) value of 8 hours. I cranked them back to two minutes and
mysqltuner stopped complaining.
mysqltuner also tells me "Total buffers: 58.0M global + 2.7M per thread" which implies that cutting
back on max_connections (number of threads) will have quite a limited impact. But my memory is really
tight, so let's do that anyway. I went with 10 for max_connections: 5 Apache servers plus padding for
phpmyadmin connections, among other things. (mysqltuner will warn me in the future if I am bumping up
against this limit.)
mysqltuner also suggests increasing some memory constraints on certain MySQL functions, but I am
going to hold off on that until I see what happens with the current setup for a couple of days.
[1] also talks about turning off the InnoDB engine with "skip-innodb" in order to save a pile of
memory, but one of my databases is using InnoDB tables, and when I try to convert them to myisam it
refuses while complaining about "foreign keys". I believe that database is using an InnoDB feature
where, if I delete a row in one table dependent rows in other tables are also automatically deleted, so
I guess I will live with it for the moment.
[1] http://www.360doc.com/content/07/1210/14/15540_883954.shtml
posted at: 03:02 | path: /Admin/databases/MySQL | permanent link to this entry
Fri, 18 Sep 2009
/Admin/databases/MySQL:
phpmyadmin: Mixed Review
Generally speaking I find phpmyadmin to be a very handy piece of software, but today my enthusiasm is
tempered (and the command line still rules!) because I spent a fair bit of time bumping up against
phpmyadmin's limitations:
Problem #1: in all the confusion it is very hard to be absolutely positive, but I am pretty sure
an imported mysqldump (imported with phpmyadmin) was corrupted.
Problem #2: phpmyadmin advertises a capability to rename a database. Apparently this was
actually a part of MySQL for only a short time before it was ripped out, and not unsurprisingly it does
not work for the version of MySQL that I am using. It actually fails rather sloppily, creating a database
with the new name but short some tables, and throws an obtuse error message.
The recommended method for renaming a MySQL database is apparently a mysqldump, followed by an import
into the new database.
posted at: 14:09 | path: /Admin/databases/MySQL | permanent link to this entry
Fri, 27 Feb 2009
/Admin/databases/MySQL:
Setup MySQL to use SSL for Remote Connections
Some good advice in an age of people, companies, and governments
avaricious to acquire / store / use / sell your personal information: use
encryption wherever possible when communicating over networks.
Here[1] is a nice concise guide to the basics of getting SSL working on
MySQL[2].
First login to MySQL and check for SSL support:
# mysql -p
Enter password:
mysql> show variables like 'have_ssl';
You should see "DISABLED" at this point, since you have not set it up
yet. (If the response says anything other then "DISABLED" or "YES", then
your MySQL server has probably been compiled without SSL support. Not a
problem on Debian....)
Then Enable SSL Support in the Server:
To avoid a rather dauntingly complex series of openssl command lines, I
opted to use tinyca ("apt-get install tinyca") to provide a GUI front-end to
openssl on my desktop. Basically, with tinyca the process of generating the
files required for SSL is a rather short and simple point-n-click process,
and seems to work just fine if you fill in the absolute minimum information
in the forms. For the record, I also chose a 1024 bit key and made the
"common names" on the CA and the certificate different.
After creating CA, certificate, and key, export them all. Do export the
key without a password. (I did verify that a key exported with a password
causes SSL support in MySQL to silently fail, and log the failure to
/var/log/syslog.) scp the three files to your server, copy them to
/etc/mysql, change their ownership to the mysql user and "chmod 600", then
in /etc/mysql/my.cnf uncomment and add them to the appropriate lines:
ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/my-new-server-cert.pem
ssl-key=/etc/mysql/my-new-server-key.pem
After restarting MySQL,
mysql> show variables like 'have_ssl';
should result in a "YES".
Now Get MySQL clients Working:
Test a client using SSL on the MySQL server. Create a temporary user for
the test:
mysql> GRANT ALL on databasename.* TO 'ssluser'@'localhost' IDENTIFIED BY
'thispassword' REQUIRE SSL;
From a terminal on the MySQL server, try logging in with this user:
mysql -ussluser -p --ssl-ca=/etc/mysql/cacert.pem
No errors means SSL is working! Delete the test user:
mysql> DELETE FROM mysql.user WHERE user='ssluser' and host='localhost';
And still on the MySQL server, create a user for remote access, from a
specific IP address only:
mysql> GRANT ALL on databasename.* TO 'SSLremote'@'153.129.49.127'
IDENTIFIED BY 'thispassword' REQUIRE SSL;
On the remote client (IP address 153.129.49.127) presumably your desktop,
try to login over SSL:
mysql -uSSLremote -pthispassword -hwww.mysqlserverhost.com
--ssl-ca=/home/user/cacert.pem
If it works, mission accomplished!
Just in case anyone else might share my bright idea that it should be
possible to coax phpmyadmin into using this MySQL SSL connection, it would
appear to be not easy. This post[3] suggests a patch is necessary. And in
fact, I grepped the phpmyadmin source currently in my /usr/share/phpmyadmin
for "mysqli_ssl_set" and turned up no hits. So the command line it is, or
maybe a little custom Python web app....
[1] http://blog.aisleten.com/2008/05/25/connecting-to-mysql-using-ssl-encryption-in-ruby-on-rails/
[2] http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html
[3] http://sourceforge.net/tracker/index.php?func=detail&aid=1746131&group_id=23067&atid=377411
posted at: 04:35 | path: /Admin/databases/MySQL | permanent link to this entry
Sun, 22 Feb 2009
/Admin/databases/MySQL:
Enabling Remote Connections to a MySQL Database
This is not straight-forward, and thanks to this post[1] for getting me
pointed in the right direction.
Firewall: open port 3306.
Configuration: ensure both of
- bind-address = 127.0.0.1 (Debian default) and
- skip-networking
are commented out in /etc/mysql/my.cnf. These are security options meant
to confine MySQL server access to the local machine only.
Grant Access: MySQL has one more layer of security: access to a
specific database can be explicitly granted to a specific user at a
specific IP (host). There are, of course, wild cards that permit making the
access wide-open, but why not be secure:
GRANT ALL privileges ON databasename.* TO username@'123.119.49.127'
IDENTIFIED BY 'password';
This command appears to create username, or modify an existing username.
[1] http://ubuntuforums.org/showthread.php?t=608435
posted at: 08:14 | path: /Admin/databases/MySQL | permanent link to this entry
