Difference between revisions of "MySQL"

Latest revision as of 00:21, 10 February 2021

MySQL is a multithreaded, multi-user, SQL (Structured Query Language) Database Management System (DBMS).

see: MySQL/scripts for examples.

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:

http://www.mysql.com/downloads/mirrors.html

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