Installation

Packages

apt-get install mysql-server mysql-client                 # Debian, Ubuntu
    yum install mysql-server mysql                        # Fedora

Source

Prerequisites, as root:

apt-get install bzr cmake make g++ bison m4 libssl-dev               # Debian, Ubuntu
yum install bzr cmake make gcc-c++ bison m4 openssl-devel            # Fedora, RedHat, CentOS
zypper install bzr cmake make gcc-c++ bison m4 openssl-devel         # openSUSE

groupadd mysql
useradd --system --group mysql mysql

The MySQL source can be checked out with Bazaar:

bzr checkout --verbose lp:mysql-server mysql-server-bzr

Note: the lp is actually a shortcut, the repo's full address can be found in .bzr/branch/branch.conf.

Let's build the source:

cd mysql-server-bzr

cmake . -LH
cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DWITH_LIBWRAP=yes -DWITH_SSL=yes

make
make test

sudo make install

When building on an NFS-share, make install might not work right away:

sudo mkdir /opt/mysql
sudo chown alice /opt/mysql
make install
sudo chown -R mysql:mysql /opt/mysql

Configuration

`my.cnf`

A minimal my.cnf could look like this:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock

port            = 3306
# Set to /usr for most distributions
basedir         = /opt/mysql
datadir         = /var/lib/mysql
tmpdir          = /var/run/mysqld
bind-address    = 127.0.0.1

character_set_server = latin1
collation_server     = latin1_german1_ci

# Logging
# log                   = /var/log/mysql/queries.log
log-error               = /var/log/mysql/mysql.err
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
slow_query_log_file     = /var/log/mysql/mysql-slow.log
slow_query_log          = 1
long_query_time         = 2
log-queries-not-using-indexes = 1

# enable binlog
server-id               = 1
log_bin                 = /var/log/mysql/binlog/mysql-bin.

# Advised for MySQL 5.6.6
# explicit_defaults_for_timestamp = 1

# When disabled, the following message is supressed during startup:
# > [Note] InnoDB: The InnoDB memory heap is disabled
#
# However, the following message appears instead:
# > InnoDB: Warning: Setting innodb_use_sys_malloc to FALSE is DEPRECATED. 
# > This option may be removed in future releases, together with the InnoDB's internal memory allocator.
#
# innodb_use_sys_malloc   = 0

Usage

MySQL is best started via mysqld_safe:

$ /opt/mysql/bin/mysqld_safe --defaults-file=/root/my.cnf

$ ps -ef | grep [m]ysql
root  13467 29491  0 22:56 pts/0 00:00:00 /bin/sh /opt/mysql/bin/mysqld_safe --defaults-file=/root/my.cnf
mysql 13802 13467  1 22:57 pts/0 00:00:00 /opt/mysql/bin/mysqld --defaults-file=/root/my.cnf \
        --basedir=/opt/mysql --datadir=/var/lib/mysql --plugin-dir=/opt/mysql/lib/plugin \
        --user=mysql --log-error=/var/log/mysql/mysql.err --pid-file=/var/run/mysqld/mysqld.pid \
        --socket=/var/run/mysqld/mysqld.sock --port=3306

Initialization

Before starting the database for the first time, several tables have to created:

mysqld --user=mysql --initialize --basedir=/opt/mysql --datadir=/var/lib/mysql

Once this is completed, the following is printed:

[Note] A temporary password is generated for root@localhost: p$ssw0rd

Execute mysql_secure_installation to (re)set the root password and everything should be good to go. If needed, a ~/.my.cnf configuration file can be installed too:

$ cat ~/.my.cnf 
[client]
socket          = /var/run/mysql/mysql.sock
user            = root
password        = p$ssw0rd

The password can be changed via SQL too:^[1]

> ALTER USER root@localhost IDENTIFIED BY 's3cr3t';

Create a user and a database

Create a new user, a new database - and grant our new user permissions (only) on this new database:

mysql> create user dummy@localhost identified by 's3cr3t';
mysql> create database testdb;
mysql> grant all privileges on testdb.* to dummy@localhost;

Let's try:

$ mysql -u dummy -p
Enter password: 

mysql> use mysql
Database changed
mysql> select * from db;
ERROR 1142 (42000): SELECT command denied to user 'dummy'@'localhost' for table 'db'

Great :-) Let's create a table too:

mysql> create table users (id int auto_increment primary key not null, name text not null, email text not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into users (name, email) values ('alice', 'alice@example.org');
Query OK, 1 row affected (0.01 sec)

mysql> insert into users (name, email) values ('bob', 'bob@example.org');
Query OK, 1 row affected (0.03 sec)

mysql> select * from users;
+----+-------+-------------------+
| id | name  | email             |
+----+-------+-------------------+
|  1 | alice | alice@example.org |
|  2 | bob   | bob@example.org   |
+----+-------+-------------------+
2 rows in set (0.00 sec)

The last command again, with a different output formatting:

mysql> select * from users \G
*************************** 1. row ***************************
   id: 1
 name: alice
email: alice@example.org
*************************** 2. row ***************************
   id: 2
 name: bob
email: bob@example.org
2 rows in set (0.00 sec)

Note: the hosts table can no longer be used for access control.^[2]

Example Database

There are several example databases to choose from, e.g.:

mysql -p -e 'create database world'
curl http://downloads.mysql.com/docs/world.sql.gz | gzip -dc | mysql -D world -p

Change `datadir`

Apparently the datadir cannot be changed^[3] that easily:

$ grep datadir /etc/mysql/my.cnf
# datadir       = /var/lib/mysql
datadir         = /data/mysql

Yet, MySQL fails to start as it still expects ibdata1 in the old datadir:

140531  2:40:12 InnoDB: Completed initialization of buffer pool
140531  2:40:12  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: File name /var/lib/mysql/ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
140531 02:40:12 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

The trick was to add a symlink at the old datadir to the new one:

sudo ln -s /data/mysql /var/lib/mysql

Fiddling around with innodb_data_home_dir or innodb_log_group_home_dir did not help so far.

Database size

mysql> SELECT sum(data_length + index_length) / 1024 / 1024 AS size FROM information_schema.tables \
       WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql');
+--------------+
| size         |
+--------------+
| 488.87791538 |
+--------------+
1 row in set (0.48 sec)

Group by database:

mysql> SELECT table_schema AS db, sum(data_length + index_length)/1024/1024 AS size
       FROM information_schema.tables GROUP BY table_schema ORDER BY size DESC;
+--------------------+--------------+
| db                 | size         |
+--------------------+--------------+
| blog               | 317.07045078 |
| mediawiki          |  89.07272339 |
| accounts           |  81.97357941 |
| mail               |   0.56250000 |
+--------------------+--------------+
9 rows in set (0.30 sec)

Group by table:

mysql> SELECT table_name AS tbl, round(((data_length + index_length) / 1024 / 1024), 2) AS size \
       FROM information_schema.TABLES WHERE table_schema = "DBNAME" ORDER BY size DESC LIMIT 3;
+--------------------------------+--------+
| tbl                            | size   |
+--------------------------------+--------+
| blog_spamblocklog              | 370.11 |
| blog_visitors                  |  33.88 |
| blog_shoutbox                  |   4.68 |
+--------------------------------+--------+
3 rows in set (0.03 sec)

Group by table over all databases:^[4]

mysql> SELECT table_schema AS db, table_name AS tbl, round(((data_length + index_length) / 1024 / 1024), 2) size \
       FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC LIMIT 3;
+------------------+--------------------------+--------+
| db               | tbl                      | size   |
+------------------+--------------------------+--------+
| blog             | blog_spamblocklog        | 370.11 |
| mediawiki        | text                     |  89.55 |
| blog             | blog_visitors            |  33.88 |
+------------------+--------------------------+--------+
3 rows in set (0.03 sec)

Alter Table Names

For example, if we want to remove a table prefix from all the tables, we can generate the necessary statements^[5] like this:

select 'RENAME TABLE ' || table_name ||  ' TO ' || substr(table_name, 5) ||';' from information_schema.tables

Or, somewhat more elaborate:

select concat('RENAME TABLE ', concat(table_name, concat(' TO ', concat(substr(table_name, 5), ';')))) from information_schema.tables;

Backup

`mysqlhotcopy`

NOTE: mysqlhotcopy works only for MyISAM and ARCHIVE tables!

mysqlhotcopy db1 /backup/mysql/

In effect, mysqlhotcopy just copies datadir to /backup/mysql/.

`mysqldump`

mysqldump -u backup -h localhost \
       --delete-master-logs \
       --lock-all-tables \
       --triggers \
       --routines \
       --all-databases \
       --flush-logs \
       --complete-insert \
       --default-character-set=latin1 \
       --ignore-table=performance_schema.cond_instances \
       --ignore-table=performance_schema.events_waits_current \
       --ignore-table=performance_schema.events_waits_history \
       --ignore-table=performance_schema.events_waits_history_long \
       --ignore-table=performance_schema.events_waits_summary_by_instance \
       --ignore-table=performance_schema.events_waits_summary_by_thread_by_event_name \
       --ignore-table=performance_schema.events_waits_summary_global_by_event_name \
       --ignore-table=performance_schema.file_instances \
       --ignore-table=performance_schema.file_summary_by_event_name \
       --ignore-table=performance_schema.file_summary_by_instance \
       --ignore-table=performance_schema.mutex_instances \
       --ignore-table=performance_schema.performance_timers \
       --ignore-table=performance_schema.rwlock_instances \
       --ignore-table=performance_schema.setup_consumers \
       --ignore-table=performance_schema.setup_instruments \
       --ignore-table=performance_schema.setup_timers \
       --ignore-table=performance_schema.threads | \
       xz -c > backup.sql.xz

Notes:

--delete-master-logs enables --master-data and thus needs the RELOAD privilege. Also, binary log must be enabled.

Ignore tables with the PERFORMANCE_SCHEMA schema, as they'd produce an error during mysqldump:

Couldn't execute 'show create table `cond_instances`': Unknown table engine 'PERFORMANCE_SCHEMA' (1286)

If character_set_server is set to latin1, we better set default-character-set for mysqldump to avoid character conversion.

 mysql> show variables like "%char%";
 +--------------------------+----------------------------+
 | Variable_name            | Value                      |
 +--------------------------+----------------------------+
 | character_set_client     | latin1                     |
 | character_set_connection | latin1                     |
 | character_set_database   | latin1                     |
 | character_set_filesystem | binary                     |
 | character_set_results    | latin1                     |
 | character_set_server     | latin1                     |
 | character_set_system     | utf8                       |
 | character_sets_dir       | /usr/share/mysql/charsets/ |
 +--------------------------+----------------------------+

Copy

Make a copy^[6]

mysqldump db1 > dump.sql
mysqladmin create db2
mysql db2 < dump.sql

Restore

Restore from an SQL dump:

mysql -u user -p < backup.sql

Restore only one database from an SQL dump containing lots of databases^[7]:

mysql -u user -p --one-database DBNAME < backup.sql

Extract only the backup for DBNAME from the SQL dump:

sed -n '/^-- Current Database: `DBNAME`/,/^-- Current Database: `/p' backup.sql > backup_DBNAME.sql

Replication

On the MASTER server, we have to set server-id:

$ grep -B1 server-id /etc/mysql/my.cnf
[mysqld]
server-id=2

Still on the MASTER, we grant replicator@localhost REPLICATION SLAVE rights to all databases:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'localhost' IDENTIFIED BY 's3cr3t';

With that in place, we lock all the tables and get the (our) master status:

mysql> flush tables with read lock;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      123 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

On the SLAVE now, we configure for the actual replication:

mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=9191,MASTER_USER='replicator',\
                        MASTER_PASSWORD='s3cr3t', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=123;

Since we're connecting to a different port (actually SSH-tunneled to some other device), we have to modify our SELinux policy:

setsebool -P mysql_connect_any 1

Connectors

JDBC

For OpenOffice:

Perferences -> Java
Click "CLASSPATH"
Click "Add Archive"
add mysql-connector-java-x.x.x-bin.jar
Click OK, restart OpenOffice

Click "Connect to an existing database" with the drop down set to JDBC.
Click Next.
jdbc:mysql://username:password@servername:port/databasename
Driver Class is com.mysql.jdbc.Driver

We can now test and connect to our database.

ODBC

tbd...

Maintenance

myisamchk

myisamchk is used to check, repair or optimize tables while MySQL is stopped.

sudo service mysql stop
myisamchk --read-only --extend-check --silent /var/lib/mysql/*/*.MYI

mysqlcheck

mysqlcheck is used to check, repair, optimize or analyze tables while MySQL is running.

$ mysqlcheck --all-databases --check --extended --silent
foo.table1                             OK
foo.table2                             OK
bar.table1
note     : The storage engine for the table doesn't support repair

Note: use --repair instead of --check to actually repair the tables. However, --repair will not work for InnoDB tables!

Troubleshooting

`ib_logfile0`

MySQL exits with:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
130121  1:57:35 [ERROR] Plugin 'InnoDB' init function returned error.
130121  1:57:35 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130121  1:57:35 [ERROR] Unknown/unsupported storage engine: InnoDB
130121  1:57:35 [ERROR] Aborting
130121  1:57:35 [Note] /usr/libexec/mysqld: Shutdown complete

This can happen when innodb_log_file_size has (been) changed. A simple solution would be:

Shutdown mysqld
Remove ib_logfile*
Start mysqld

Query Log

Temporarily enable to query log:

$ mysql -u root -p -e "set global general_log = on;"
Enter password:

$ mysql -u root -p -e "show variables where variable_name like '%general%';"
Enter password:
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| general_log      | ON                       |
| general_log_file | /var/log/mysql/mysql.log |
+------------------+--------------------------+

Links

Benchmark

Die wunderbare Welt von Isotopp (Archive) Linkdump:
- Zeichensatzaerger (Archive)
- MySQL fuer Dummies 1 (Archive)
- MySQL fuer Dummies 2 (Archive)
- MySQL fuer Dummies 3 (Archive)
- MySQL fuer Dummies 4 (Archive)
- MySQL fuer Dummies 5 (Archive)
- MySQL fuer Dummies 6 (Archive)
- MySQL fuer Dummies 7 (Archive)

How to safely change MySQL innodb variable 'innodb_log_file_size'? - useful when you get those "ib_logfile0 is of different size" errors.
Bug #68035 / innodb claims: CPU does not support crc32 instructions

mk-table-checksum - Perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers (now redirects to the Percona Toolkit for MySQL)

Using Operating System Memory Allocators ("The InnoDB memory heap is disabled")

mysql2sqlite: Converts MySQL dump to SQLite3 compatible dump

References

[1] SET PASSWORD Statement

[2] As of MySQL 5.6.7 the host table is no longer included in the installation of MySQL.

[3] How to change MySQL data directory?

[4] How to get the sizes of the tables of a mysql database?

[5] How to remove a prefix name from every table name in a mysql database

[6] Making a Copy of a Database

[7] Restoring a single database from a complete MySQL database dump

[1]

[2]

[3]

[4]

[5]

[6]

[7]

MySQL

Contents

Installation

Packages

Source

Configuration

`my.cnf`

Usage

Initialization

Create a user and a database

Example Database

Change `datadir`

Database size

Alter Table Names

Backup

`mysqlhotcopy`

`mysqldump`

Copy

Restore

Replication

Connectors

JDBC

ODBC

Maintenance

myisamchk

mysqlcheck

Troubleshooting

`ib_logfile0`

Query Log

Links

References

Navigation menu

MySQL

Installation

Packages

Source

Configuration

my.cnf

Usage

Initialization

Create a user and a database

Example Database

Change datadir

Database size

Alter Table Names

Backup

mysqlhotcopy

mysqldump

Copy

Restore

Replication

Connectors

JDBC

ODBC

Maintenance

myisamchk

mysqlcheck

Troubleshooting

ib_logfile0

Query Log

Links

References

Navigation menu

`my.cnf`

Change `datadir`

`mysqlhotcopy`

`mysqldump`

`ib_logfile0`