Over the years I’ve been accumulating notes on how to do quick operations in a variety of technologies. These have been, up to now, for my eyes only, and they aim to be the documentation I wish I found online on how to quickly get something done in tech X. Today I decided to share some notes of one of those technologies: MySQL.
Disclaimer: These notes are valid at the moment of writing and not for all versions of MySQL (I am testing 5.7.17 on macOS) as things continuously change.
Disclaimer 2: You are responsible for your data. Read everything written here with a critical mind and only apply it once you tested it. And I can never emphasise this too much: do backups!
What you can learn from this post
- How to install, config and start MySQL server on macOS
- Quick admin commands
- Who are you on the MySQL console?
- How to see running queries and stats
- How to kill a hanging query
- How to find slow queries
- How many queries are running and how many are sleeping connections?
- How to set the timeout for queries
- How to set the max connections in MySQL CLI
- How to set the max connections in the configuration file
- How to find the maximum concurrent user connections that happened on this server
- How to configure the Innodb buffer pool
- How to set the character set in MySQL
- How to get the top 10 biggest tables in size
- How to get the oldest tables with respect to update times
- How to get the data size (in MB) of the different databases
- How to get all users in MySQL and their permissions
- How to get the permissions for user MYUSER
- How to get the permissions for user ‘website’ at IP ‘1.1.1.1’
- How to give full permission for user ‘myuser’ at localhost on the database ‘magento’
- How to revoke permissions
- How to reset the password for user MYUSER
- How to reset the root password
- How to set user quotas
- How to import bulk data faster into MySQL
- How to export a table to a csv file
- How to get the table DDL (definitions file)
- How to do backups
- How to log a session in MySQL
- How to check if a table is healthy, fragmented and how to repair it
First things, first
How to install, config and start MySQL server on macOS
- If you are just trying these out and don’t happen to have a MySQL server to connect to you can download it from here, uncompress the DMG file and click next until it’s installed.
- Unfortunately the package does not alias the commands we need for direct use on the terminal so add the following to your .bash_profile:
echo "alias mysql=/usr/local/mysql/bin/mysql" >> ~/.bash_profile
echo "alias mysqladmin=/usr/local/mysql/bin/mysqladmin" >> ~/.bash_profile
echo "alias mysqldumpslow=/usr/local/mysql/bin/mysqldumpslow" >> ~/.bash_profile
echo "alias mysqldump=/usr/local/mysql/bin/mysqldump" >> ~/.bash_profile
source ~/.bash_profile
- The only configuration we are going to make for now is to set a little file called .my.cnf to have our username and password (which was generated during the installation step). This is obviously not safe for production environments but for a playground it saves us typing the username and password every single time. So do the following:
vim ~/.my.cnfand add the following to the file:
[client]
user=root
password=yourSecurePassword
For security’s sake please change the permissions on the file so that only you can see it: chmod 0600 ~/.my.cnf.
Now let us start the server. Go to
System Preferencesand you can see that there is a new icon on the bottom line calledMySQL, click there and then onStart MySQL Server(input your password when it asks you)Go back to the terminal and type
mysqland voilá! We’re in!The password that was generated by the install package is temporary, so I would advise changing it once you’re logged in like this:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('myNewPassword');
Query OK, 0 rows affected, 1 warning (0.00 sec)
Quick admin commands
# Show all databases
show databases;
# See the SQL for the creation of a table
show create table <table>;
# See all the environment variables
show global status;
# Set the contents of a global variable
set global <VARIABLE_NAME>=<VALUE>;
# Get the contents of all variables starting with 'wait'
show global variables like 'wait%';
# Find all users on this database
select User, Host from mysql.user;
Who am I?
Not to get all philosophical on you but if you at one point forget the user you are logged in as do this to find out:
SELECT USER(),CURRENT_USER();
USER() reports how you attempted to authenticate in MySQL CURRENT_USER() reports how you were allowed to authenticate in MySQL
On queries
How to see running queries and stats
mysqladmin proc stat --verbose
+----+------+-----------+----+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+----------+-----------------------+
| 10 | root | localhost | | Query | 0 | starting | show full processlist |
+----+------+-----------+----+---------+------+----------+-----------------------+
Uptime: 404 Threads: 1 Questions: 11 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.027
This command allows us to see what’s going on without actually having to log in to the MySQL CLI. You get all the running queries, the uptime of the server, how many of those queries are slow, how many queries are being executed per second, if a query has gone zombie on you and a few others.
As you can see in the Info bit, one of the command being encapsulated by this one is
mysql> show full processlist;
This means that you can do the same things in the MySQL CLI.
How to kill a hanging query
Imagine that, from the list above, you have one query on your list that is taking forever (and by forever I mean a completely subjective number of seconds in the Time column). Then you can simply kill it like you would a process on your Mac.
mysql> show full processlist;
+----+------+-----------+------+---------+------+------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+------------+-----------------------+
| 11 | root | localhost | NULL | Query | 0 | starting | show full processlist |
| 12 | root | localhost | NULL | Query | 17 | User sleep | select sleep(100) |
+----+------+-----------+------+---------+------+------------+-----------------------+
2 rows in set (0.00 sec)
mysql> kill 12;
Query OK, 0 rows affected (0.00 sec)
How to find slow queries
MySQL not only comes with an opinion on what a “slow query” means, but also comes with a nifty little tool to parse the slow query log files and show you a summary called mysqldumpslow.
From the MySQL docs:
The slow query log consists of SQL statements that took more than
long_query_timeseconds to execute and required at leastmin_examined_row_limitrows to be examined. The minimum and default values oflong_query_timeare 0 and 10, respectively.
And a caveat:
By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed using
log_slow_admin_statementsandlog_queries_not_using_indexes
By default, slow queries are not logged. So let’s activate them!
# Activate slow query log
mysql> set global slow_query_log='ON';
# max 5 seconds
mysql> set global long_query_time='5';
# These values are valid ONLY until the database server is restarted! As an alternative do it in the config file
# If not already created, create the file
sudo touch /etc/my.cnf
# Change owner to yourself
sudo chown `whoami` /etc/my.cnf
# Add content
echo "[mysqld]" >> /etc/my.cnf
echo "slow_query_log=1" >> /etc/my.cnf
# You can also activate the following, but beware that the logfile may grow fast
echo "log_queries_not_using_indexes=1" >> /etc/my.cnf
echo "log_slow_admin_statements=1" >> /etc/my.cnf
If you want to find out where the log is going to:
mysql> show global variables like 'slow_query_log_file';
+---------------------+----------------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------------+
| slow_query_log_file | /usr/local/mysql/data/<YOUR_LAPTOP>-slow.log |
+---------------------+----------------------------------------------+
1 row in set (0.00 sec)
So now you can test this!
mysql> select sleep(11);
+-----------+
| sleep(11) |
+-----------+
| 0 |
+-----------+
1 row in set (11.01 sec)
And then sudo tail -f /usr/local/mysql/data/<YOUR_LAPTOP>-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.17 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 2017-04-09T12:57:07.073262Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 11.005275 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1491742627;
select sleep(11);
Now you can use mysqldumpslow to show you a nice summary of the slow queries of your database (once you have a few):
sudo /usr/local/mysql/bin/mysqldumpslow /usr/local/mysql/data/<YOUR_LAPTOP>-slow.log
# Or the top 10 slow queries by count
sudo /usr/local/mysql/bin/mysqldumpslow -t 10 -s c /usr/local/mysql/data/<YOUR_LAPTOP>-slow.log
# Or the top 10 slow queries by time spent
sudo /usr/local/mysql/bin/mysqldumpslow -t 10 -s t /usr/local/mysql/data/<YOUR_LAPTOP>-slow.log
How many queries are running and how many are sleeping connections?
# Running queries
mysqladmin processlist |wc -l
# Sleeping queries
mysqladmin processlist |grep -i sleep|wc -l
Configuration
How to set the timeout for queries
# What is the default duration that a query can run for?
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.01 sec)
# This means 8 hours!
# Let's set it to 10 minutes
mysql> set global wait_timeout=600;
Query OK, 0 rows affected (0.00 sec)
How to set the max connections in MySQL CLI
mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> set global max_connections=170;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 170 |
+-----------------+-------+
1 row in set (0.00 sec)
# This value is valid ONLY until the database server is restarted!
How to set the max connections in the configuration file
# If you're on a Mac, you have to create the file and add the max_connections to it
sudo touch /etc/my.cnf
# Change owner to yourself
sudo chown `whoami` /etc/my.cnf
# Add content
echo "[mysqld]" >> /etc/my.cnf
echo "max_connections=505" >> /etc/my.cnf
# Go to System Preferences and restart MySQL
# And query again
mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 505 |
+-----------------+-------+
1 row in set (0.00 sec)
How to find the maximum concurrent user connections that happened on this server
mysql> show global status like "%max_used_connections%";
How to configure the Innodb buffer pool
Ideally you want the buffer pool size to be equal to the size of your innodb data files. If you can’t get to that:
On a dedicated database server, you might set the buffer pool size to 80% of the machine’s physical memory size.
You can set the buffer pool size in the my.cnf config file like so:
[mysqld]
...
innodb_buffer_pool_size = 2G
...
Or dynamically (from MySQL v.5.7.5) on the MySQL CLI like so:
# 2G x 1024 x 1024 x 1024 to get bytes
mysql> set global innodb_buffer_pool_size=2147483648;
How to set the character set in MySQL
# Add to [mysqld] section of /etc/my.cnf:
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
Getting info
How to get the top 10 biggest tables in size
SELECT TABLE_SCHEMA, TABLE_NAME,data_length/1024*1024 FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql' ORDER BY `data_length` DESC LIMIT 10;
How to get the oldest tables with respect to update times
SELECT CONCAT (`TABLE_SCHEMA`, "." , `TABLE_NAME`) as name , `UPDATE_TIME` FROM `TABLES` WHERE `UPDATE_TIME` IS NOT NULL ORDER BY `UPDATE_TIME` LIMIT 10;
How to get the data size (in MB) of the different databases
SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
Users
How to get all users in MySQL and their permissions
SELECT * from `INFORMATION_SCHEMA`.`USER_PRIVILEGES`;
How to get the permissions for user MYUSER
SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES where grantee like '%MYUSER%';
How to get the permissions for user ‘website’ at IP ‘1.1.1.1’
SHOW GRANTS FOR website@'1.1.1.1';
How to give full permission for user ‘myuser’ at localhost on the database ‘magento’
GRANT ALL PRIVILEGES ON magento.* TO 'myuser'@'localhost';
How to revoke permissions
REVOKE SELECT,INSERT,UPDATE,DELETE ON mydatabase.* FROM 'myuser'@'localhost';
How to reset the password for user MYUSER
use mysql;
set password for '<MYUSER>'@'%' = PASSWORD('myPass');
flush privileges;
How to reset the root password
# Add this line to the beginning of /etc/my.cnf ([mysqld] section):
init_file=/var/lib/mysql/mysql.init
# Create file /var/lib/mysql/mysql.init with the following contents:
SET PASSWORD FOR 'root'@'localhost's=PASSWORD('mynewpassword');
FLUSH PRIVILEGES;
# Change the owner to the local MySQL user
chown <MYSQL_USER> /var/lib/mysql/mysql.init
# Restart the server
# Delete the mysql.init file and remove the init_file line from /etc/my.cnf
How to set user quotas
This is done through the GRANTS that the user has on the database. You can control MAX_CONNECTIONS_PER_HOUR, MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_USER_CONNECTIONS.
One example:
GRANT ALL ON mydatabase.* TO 'myuser'@'%' WITH MAX_QUERIES_PER_HOUR 100 MAX_UPDATES_PER_HOUR 10;
Import/Export
How to import bulk data faster into MySQL
# The trick here is to disable indexes for the duration of the bulk import, so...
ALTER TABLE table_name DISABLE KEYS;
# ...import...
ALTER TABLE table_name ENABLE KEYS;
How to export a table to a csv file
mysql <MYDATABASE> -e "SELECT * FROM <MYTABLE>" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > <MYTABLE>.csv
How to get the table DDL (definitions file)
mysqldump -f -d --single-transaction <MYTABLE> > ~/Desktop/createMyTable.sql
How to do backups
# Backup all databases
mysqldump --all-databases > ~/Desktop/fullBackup.sql
# Backup just mydb
mysqldump --databases mydb > ~/Desktop/mydbBackup.sql
# Backup just mytable in mydb
mysqldump mydb mytable > ~/Desktop/mytableBackup.sql
How to log a session in MySQL
mysql> tee mysession.log
Logging to file 'mysession.log'
Others
How to check if a table is healthy, fragmented and how to repair it
mysql> check table <MYTABLE>;
mysql> repair table <MYTABLE>;
I hope this helps!
If you find mistakes with these recipes, feel free to raise an issue here.