MySQL

From Segfault
Jump to: navigation, search

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:

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:

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:

/opt/mysql/scripts/mysql_install_db --user=mysql --basedir=/opt/mysql --datadir=/var/lib/mysql

Once this is completed, a warning is printed:

> PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
> To do so, start the server, then issue the following commands:
>
> /opt/mysql/bin/mysqladmin -u root password 'new-password'
> /opt/mysql/bin/mysqladmin -u root -h foo.local password 'new-password'
>
> Alternatively you can run:
>
> /opt/mysql/bin/mysql_secure_installation

For this to work, the database has to be started:

$ /opt/mysql/bin/mysqld_safe --defaults-file=/root/my.cnf &
$ /opt/mysql/bin/mysqladmin -S /var/run/mysqld/mysqld.sock -u root password
New password:
Confirm new password:

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)

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[1] 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;
+--------------------+--------------+
| db                 | size         |
+--------------------+--------------+
| test               |   0.05465698 |
| information_schema |   0.00878906 |
| mediawiki          |  89.07272339 |
| mysql              |   0.69783974 |
| performance_schema |   0.00000000 |
| phpmyadmin         |   0.14400482 |
| accounts           |  81.97357941 |
| mail               |   0.56250000 |
| blog               | 317.07045078 |
+--------------------+--------------+
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:[2]

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[3] 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:

  • 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)
 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[4]

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[5]:

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:

  1. Shutdown mysqld
  2. Remove ib_logfile*
  3. 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

References