MySQL
MySQL is a multithreaded, multi-user, SQL (Structured Query Language) Database Management System (DBMS).
see: MySQL/scripts for examples.
Contents
- 1 Install and configure
- 2 User control
- 3 Regex and string functions
- 4 Data types
- 5 Database Administration Statements
- 6 External data input
- 7 Timezone data
- 8 Howto fix "Access denied for user 'MySQL_Instance_M'"
- 9 How to identify slow MySQL queries
- 10 Automatic MySQL backup script
- 11 SuSE Linux default configuration
- 12 [OLD METHODS] Installing and Configuring MySQL
- 13 See also
- 14 External links
Install and configure
- Debian-based installs:
$ apt-get update && apt-get -y install mysql-server $ file $(which mysql_secure_installation) /usr/bin/mysql_secure_installation: Perl script, ASCII text executable $ mysql_secure_installation $ mysqladmin -u root create wordpress $ mysqladmin -u root password "mysql_password" $ mysql -uroot -pmysql_password -e "CREATE USER 'wordpress'@'%' IDENTIFIED BY 'mysql_password';" $ mysql -uroot -pmysql_password -e "GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress'@'%';" $ mysql -uroot -pmysql_password -e "FLUSH PRIVILEGES;"
- All in one step for authentication:
$ cat <<EOF | sudo mysql # Grant access to root GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION; # Grant root access to other IPs CREATE USER 'root'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; quit; EOF $ sudo systemctl restart mysql
User control
Change user password
- Login to MySQL server:
% mysql -u root -p mysql> use mysql; mysql> update user set password=PASSWORD("NEWPASSWORD") where User='stine'; mysql> flush privileges; mysql> quit;
Regex and string functions
Data types
Text types
CHAR( ) # a fixed section from 0 to 255 characters long. VARCHAR( ) # a variable section from 0 to 255 characters long. TINYTEXT # a string with a maximum length of 255 characters. TEXT # a string with a maximum length of 65535 characters. BLOB # a string with a maximum length of 65535 characters. MEDIUMTEXT # a string with a maximum length of 16777215 characters. MEDIUMBLOB # a string with a maximum length of 16777215 characters. LONGTEXT # a string with a maximum length of 4294967295 characters. LONGBLOB # a string with a maximum length of 4294967295 characters.
Number types
TINYINT( ) # -128 to 127 normal / 0 to 255 UNSIGNED SMALLINT( ) # -32768 to 32767 normal / 0 to 65535 UNSIGNED MEDIUMINT( ) # -8388608 to 8388607 normal # 0 to 16777215 UNSIGNED INT( ) # -2147483648 to 2147483647 normal # 0 to 4294967295 UNSIGNED BIGINT( ) # -9223372036854775808 to 9223372036854775807 normal # 0 to 18446744073709551615 UNSIGNED FLOAT # a small number with a floating decimal point DOUBLE( , ) # a large number with a floating decimal point DECIMAL( , ) # a DOUBLE stored as a string, allowing for a fixed decimal point
Date types
DATE # YYYY-MM-DD DATETIME # YYYY-MM-DD HH:MM:SS TIMESTAMP # YYYYMMDDHHMMSS TIME # HH:MM:SS
Misc types
ENUM( ) # short for ENUMERATION which means that each column may have one of a specified possible values. SET # similar to ENUM except each column may have more than one of the specified possible values.
You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.
SET is similar to ENUM except SET may contain up to 64 list items and can store more than one choice.
Database Administration Statements
Statements
An incomplete list of useful admin statments:
SHOW STATUS; SHOW FULL PROCESSLIST; SHOW VARIABLES; SHOW VARIABLES LIKE '%version%'; SHOW INNODB STATUS; SHOW OPEN TABLES;
Tools
- Checking status, etc.
shell> mysqlshow shell> mysqlshow -u root -p mysql shell> mysqladmin version status proc
- to watch incremental changes to the query cache:
mysqladmin extended -r -i 10 | grep Qcache
mysql_explain_log [options] < log_file mysqlshow [options] [db_name [tbl_name [col_name]]] ...
etc. (see: for more)
The MySQL prompt
It is possible to fully configure the MySQL prompt. These settings can be automatically loaded each time you start MySQL by placing them in the MySQL config file (ie, my.cnf, usually located in the /etc directory). You can also have your own personal config file by creating a ~/.my.cnf in your home directory.
Below is an example of what you can place in your .my.cnf file:
[mysql] prompt=(\u@\h) [\d]> [client] user=foo host=127.0.0.1 # or localhost password=yourpassword
which will display the current user ("\u"), current host ("\h"), and current database ("\d") at the MySQL prompt.
Uptime
- Check mysql uptime (useful for debugging "MySQL daemon has gone away"):
$ mysqladmin -u root -p version #~OR mysql> \s
Third-party
- mtop — monitors a MySQL server showing the queries which are taking the most amount of time to complete.
- innotop — MySQL and InnoDB monitor.
- mytop — a top clone for MySQL
- mysqlreport — Perl script for making friendly reports of important MySQL status values.
- mysqlsla — Perl script for analysing general, slow, and raw MySQL statement logs.
External data input
Example:
LOAD [LOCAL] DATA INFILE 'foo.txt' INTO TABLE company FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
where [LOCAL] is optional and the following must be met:
- If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full pathname to specify its exact location. If given as a relative pathname, the name is interpreted relative to the directory in which the client program was started.
- If LOCAL is not specified, the file must be located on the server host and is read directly by the server.
Timezone data
- Make sure you have the "tzdata" package for your distro installed and then run:
$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql $ mysql -u root -p -e "flush tables;" mysql
Howto fix "Access denied for user 'MySQL_Instance_M'"
If you enable logging via /etc/my.cnf
with the following line:
log = /var/log/mysqld/mysql.log
and then see messages something like the following in the above log file:
Time Id Command Argument 090613 18:23:33 1 Connect Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES)
what is happening is that mysqlmanager regularly tries to connect to the server to ensure that it is still alive.
You can fix it by executing the following commands from the mysql prompt:
mysql> CREATE USER 'MySQL_Instance_M'@'localhost' IDENTIFIED BY 'check_connection'; mysql> GRANT USAGE ON * . * TO 'MySQL_Instance_M'@'localhost' IDENTIFIED BY 'check_connection' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
How to identify slow MySQL queries
- Edit your
/etc/my.cnf
file and add the following:
[mysqld] log-slow-queries = /var/log/mysql_slow.log # filename can be anything you like long_query_time = 5 # log any queries that take longer than 5 seconds
- Create log file and set appropriate permissions:
touch /var/log/mysql_slow.log chown mysql.root /var/log/mysql_slow.log
- Restart MySQL:
service mysql restart
Now wait a few minutes and review the log file for long queries. Either optimise or remove these queries. Make sure to either remove or comment out the two lines from the /etc/my.cnf
after optimising, as they will slow down your server.
Automatic MySQL backup script
The simplest way to automatically backup your entire MySQL database(s) (say, once a day at 02h30) is to create a cron job like so:
30 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /backup/dir/database_$(date 'Y-%m-%d').sql.gz
Note: For more on the mysqldump
command, visit mysqldump — A Database Backup Program for extensive documentation.
Using a PHP script
The guys over at Dagon Design have written a nice little PHP script to automatically backup your MySQL databases.
You will need the following files:
You can then place this script in a crontab:
crontab -e 30 2 * * * php /root/scripts/backup_dbs.php > /dev/null 2>&1
which will perform the backup every day at 02h30 ("2:30 am").
SuSE Linux default configuration
Note: SuSE's default install is a major pain!
- Binaries: /usr/bin
- Datadir: /var/lib/mysql/
- Config file: /etc/my.cnf (note: user config file is ~/.my.cnf)
- Start/Stop MySQL: /etc/init.d/mysql {start|stop|status|reload|restart|try-restart|force-reload}
- Check if MySQL is running: netstat -ln | grep mysql
[OLD METHODS] Installing and Configuring MySQL
In this section, I will show how to install and configure MySQL for a LAMP-based system. This is not only the system I recommend (it is OSS) and I am highly biased towards, it is the only system I will discuss. If you use any other system, consulte the Web or the many books available for purchase.
Installing
The official MySQL website is http://www.mysql.com. They have many excellent resources to offer and this article is not intended to replace any of these. The goal of this article is to offer a simple step-by-step process of getting MySQL up-and-running on a LAMP-based system.
An RPM installation is the simplest and officially recommended way of installing (and upgrading) MySQL. I like to use the binary installation process because it offers a bit more control on a platform-specific optimized distribution. This article will only discuss the binary installation process.
STEP#1: Choose the closest (geographical) mirror to you from the following URL:
I use Linux (x86, glibc-2.2, "standard" is static, gcc) - Standard and, as of writing, it is around 30 MB. The "Standard" version should suit the needs of a standard user.
STEP#2: Terminal commands
Execute each of the following commands as the superuser ('root') of your system:
shell> groupadd mysql shell> useradd -g mysql mysql shell> cd /usr/local shell> gunzip < /PATH/TO/MYSQL-VERSION-OS.tar.gz | tar xvf - shell> ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql shell> cd mysql shell> scripts/mysql_install_db --user=mysql shell> chown -R root . shell> chown -R mysql data shell> chgrp -R mysql . shell> bin/mysqld_safe --user=mysql &
Installation is complete.
Configuring
Set the MySQL Administrator Password
shell> mysql -u root mysql mysql> SET PASSWORD FOR root@localhost=PASSWORD('secret'); mysql> FLUSH PRIVILEGES;
Adding New User Accounts to MySQL
mysql> GRANT ALL PRIVILEGES ON *.* -> TO 'sandusr'@'localhost' -> IDENTIFIED BY 'some_pass' -> WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; #~OR~ mysql> GRANT ALL PRIVILEGES ON sandbox.* TO 'sandusr'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION; FLUSH PRIVILEGES;
For a more specific creation and privilege control, use any of the following:
shell> mysql -u root -p mysql mysql> INSERT INTO user -> VALUES('localhost','monty',PASSWORD('some_pass'), -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO user -> VALUES('%','monty',PASSWORD('some_pass'), -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO user SET Host='localhost',User='admin', -> Reload_priv='Y', Process_priv='Y'; mysql> INSERT INTO user (Host,User,Password) -> VALUES('localhost','dummy',''); mysql> FLUSH PRIVILEGES;
Create a new database and set user permissions
- Create a new database for your site.
mysqladmin -u username -p create databasename
MySQL prompts for the 'username' database password, and creates the initial database files.
- Log in and set the access database rights:
mysql -u username -p
MySQL prompts for the 'username' database password.
- At the MySQL prompt, set the permissions using the following command:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES ON `databasename`.* TO 'username'@'localhost' IDENTIFIED BY 'password';
- Now activate the new permissions with the following command:
FLUSH PRIVILEGES;
Datadir
I like to keep all of my user-generated data on a separate disk partition (usually /home). (This assumption will be made for the remainder of this article.) I like to treat mysql as any other user and give it a space under /home/mysql. This will be true for my database files.
shell> mkdir /home/mysql/data shell> mv /usr/local/mysql/data/* /home/mysql/data # Make sure to shutdown mysql first!
Then MySQL will have to be passed the location of the database files every time you start MySQL:
shell> ./bin/mysqld_safe --datadir=/home/mysql/data --user=mysql &
Starting and Stopping MySQL
- Starting
shell> /etc/init.d/mysql start
Or,
shell> cd INSTALL-DIR shell> ./bin/mysqld_safe --datadir=/home/mysql/data --user=mysql &
Or,
/usr/local/mysql/bin/mysqld_safe \ --user=mysql \ --datadir=/home/mysql/data \ --socket=/tmp/mysql.sock \ --tmpdir=/tmp &
- Stopping
shell> /etc/init.d/mysql stop </pref> Or, shell> cd INSTALL-DIR shell> ./bin/mysqladmin -u root -p shutdown
See also
- LAMP
- LAMP on Mandriva
- tuning-primer (a script)
External links
- MySQL — Offical website
- Developing SQL Statements — on IBM
- MySQL Reference Manual - "LOAD DATA INFILE" entry
- 10 tips for optimizing MySQL queries
- SQL Injection Walkthrough
- MySQLForge
- mytop —
top
for MySQL - wikipedia:MySQL
- wikibooks:Programming:MySQL