MySQL

MySQL

Article by Joseph Purcell on April 6, 2012, last modified on April 21, 2014

For beginners, read Tech Pro's "10 Easy Steps to a Complete Understanding of SQL".

Users

Change User Password

1. Login to MySQL as any user (or root):

mysql -u root -p

2. Select the mysql database:

use mysql;

3. Run query to update the password:

update user set password=PASSWORD("my_new_password") where User="your_user";

Source: http://www.cyberciti.biz/faq/mysql-change-user-password/

Create User

Create user with grant to a specific database:

GRANT ALL ON the_database.* TO 'the_user'@'localhost' IDENTIFIED BY 'the_password';

Create user with grant to all databases:

GRANT ALL ON *.* TO 'the_user'@'localhost' IDENTIFIED BY 'the_password';

Note: be sure to run 'FLUSH PRIVILEGES'.

Delete user

DROP USER the_user;

Grant Privileges on Database to User

The same commands for "Create User" .

Grant to a specific database:

GRANT ALL ON the_database.* TO 'the_user'@'localhost';

Grant to all databases:

GRANT ALL ON *.* TO 'the_user'@'localhost';

Note: be sure to run 'FLUSH PRIVILEGES'.

Show Privileges for a User

mysql> SHOW GRANTS FOR the_user

http://serverfault.com/questions/117525/how-can-i-show-users-privileges-in-mysql

Revoke Privileges for a User

REVOKE ALL PRIVILEGES ON *.* FROM 'the_user'@'localhost';

http://serverfault.com/a/115954

http://www.mysqltutorial.org/mysql-revoke.aspx

Create a Read-Only User (i.e. for a backup user)

For example, say you want to create a user that should have read-only privileges for backing up:

GRANT SELECT,LOCK TABLES ON *.* TO 'the_user'@'localhost';

You will need the user to have the ability to lock tables, else a mysqldump may error.

Note: be sure to run 'FLUSH PRIVILEGES'.

Allow Access From Remote IP

Assuming you have done all the setup such as opening port 3306 and such, simply change 'localhost' to the IP address:

GRANT ALL ON *.* TO 'the_user'@'192.168.0.100' IDENTIFIED BY 'the_password';

Or, to allow from a domain:

GRANT ALL ON *.* TO 'the_user'@'mysite.com' IDENTIFIED BY 'the_password';

Or, to allow from any IP address or domain:

GRANT ALL ON *.* TO 'the_user'@'%' IDENTIFIED BY 'the_password';

Note that the percent sign is a wildcard, so you could do '%.mysite.com' or even '192.168.0.%'.

If the server the database is on isn't already configured, see the following link for setup instructions, see: http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html.

Source: http://stackoverflow.com/a/8348560/990642

Recover Root Password

$ sudo /etc/init.d/mysql stop
$ sudo mysqld_safe --skip-grant-tables &
$ mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("P@55VV0R|>") where User='root';
mysql> flush privileges;
$ sudo /etc/init.d/mysql restart

Source: http://www.cyberciti.biz/tips/recover-mysql-root-password.html

View Login Attempts

http://www.mysqlperformanceblog.com/2012/12/28/auditing-login-attempts-in-mysql/

Export and Import

Export

To export a single database, do:

$ mysqldump -u root -p the_database > the_file.sql

To export all databases, say for a server backup in crontab, you may do something like:

$ mysqldump -u root -pYourPassword --all-databases > the_file.sql

Import

$ mysql -u root -p the_database < the_file.sql

Auto Increment

If a query hits the "ON DUPLICATE KEY UPDATE" it will increase the auto increment count.

Configuration

When looking to configure your /etc/my.cnf, look to:

http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html

This page in documentation for other versions can be found via something like:

MySQL 5.1 Reference Manual -> 5 MySQL Server Administration -> 5.1 The MySQL Server -> 5.1.1 Server Option and Variable Reference

Remember to actually click on the option to find out how it is used in the option file (my.cnf).

Debugging

MySQL Binary Log

The MySQL binary log keeps track of every query that is run on your system, if it is enabled. It is a great place to look for finding any queries that may have caused trouble to your system. The usage is straightforward:

$ mysqlbinlog binary-log.000251

And, it will begin to spit out every query run. You can narrow the output by using the various options given by the --help flag, or consulting the MySQL documentation. However, sometimes that isn't enough.

grep is a personal favorite of mine. Using it, you could iterate through a range of binary logs and filter out all the lines that are irrelevant to what you are looking for. For example, let's say you are looking for all queries that modified the "first_name" column, you could do something like this:

for x in binary-log.00025*
do
    echo "Reading "$x"..."
    file="mysql-log-"$x".txt"
    mysqlbinlog $x | grep -C 5 "first_name" > ~/$file
done

You could accomplish the same thing using "--to-last-log", but this will split up the files for you.

Errors

"Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs"

This error is likely to occur when there are many BLOB or TEXT fields on a table using the InnoDB storage engine. Alistair Davidson wrote a great blog post about this issue, but I will summarize the problem and solution here.

The problem is that each row in a table using InnoDB has a limit of about 8000 bytes, or whatever the "innodb_page_size" is set to. Also, by default InnoDB uses the Antelope file type which will save up to 768 bytes of the BLOB or TEXT field on the row itself in addition to a pointer to where the data is stored off page. As Peter Zaitsev explains on the MySQL Performance Blog, this will cause strange storage problems: "you can store 200K BLOB easily, however you can’t store 20 of 10K blobs."

Option 1: Use a different storage engine

For example, use MyISAM. This may not be an option if you need transactions.

Option 2: Increase the "innodb_page_size"

I haven't tried this option and it doesn't seem like a good option, but here are two references anyway:

http://www.mysqlperformanceblog.com/2006/06/04/innodb-page-size/

http://www.mysqlperformanceblog.com/2011/04/21/innodb-page-sizes-plans-and-ideas/

Option 3: Use the "Barracuda" file format

This is the option you will most often find associated with this problem. As Alistair Davidson wrote, you can change the file type used to Barracuda, and alter the table to use the "DYNAMIC" or "COMPRESSED" row formats. Here are the steps:

Add the following two lines to your MySQL config (my.cnf):

innodb_file_format=Barracuda
innodb_file_per_table=ON

Alternatively, you can just run:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;

Then, alter your table to use the "DYNAMIC" or "COMPRESSED" format:
```
ALTER TABLE `the_table` ROW_FORMAT=DYNAMIC;
```

The main difference between DYNAMIC and COMPRESSED is that COMPRESSED will use zlib compression on your data.

Further Reading:

Option 4: Some other option

Fernando Ipar lists a few more options on the MySQL Performance Blog:

Limit the size of variable length columns

Use the COMPRESS/UNCOMPRESS functions

Split the table in a way that you don’t have more than 10 variable length columns per table

Combine all your variable length fields into a single BLOB and do the splitting at the application level.