Category Archives: MySQL

MySQL Performance – Howto – part 1 (high performance tuning scripts)

Often the server admin has little control over the applications which uses MySQL and it is hard to find the bottlenecks. This blog post can’t bring the peace in the world, or help NASA to finally land on the Mars. Instead those tasks, I’ll try to solve something else and present my own experiences with MySQL storage engines (at least for MyISAM and InnoDB as the most popular).
Continue reading MySQL Performance – Howto – part 1 (high performance tuning scripts)

Reducing ibdata1 – howto

One of the biggest mistakes you can make with MySQL is to leave its default configuration. It will work, but not as it should.

For example, default MySQL installation (5.0x) will keep all InnoDB data in one file – ibdata1. This file is usually located in /var/lib/mysql (at least on RH and Debian based distros) and after few months this file can became very big (in my case it was 20GB). This file has a initial size of 10Mb and it automatically extends but it can’t be reduced with DELETE, TRUNCATE or DROP. The file could reach the maximum size allowed by the filesystem if no limit is set in the my.cnf file (Debian/Ubuntu -/etc/mysql/my.cnf or RH based distros /etc/my.cnf). The best idea is to force MySQL to create an ibd file for each InnoDB table (add innodb_file_per_table in my.cnf and restart mysql). Unf. this won’t affect old tables which are already created and which are in use.

You can “FIX” this on several ways but no matter what option do you choose, BACKUP YOUR WHOLE MYSQL DATA DIRECTORY and stop services connected to your MySQL server (httpd, radius, postfix, dovecot, etc).

Keep in mind that converting InnoDB table to MyISAM will kill foreign keys so do not do it unless you know how to recreate your foreign keys.

The best option is to dump all your databases in one sql file.

Step 1

# /usr/bin/mysqldump ––extended-INSERT ––all-DATABASES ––add-drop-DATABASE ––disable-KEYS ––flush-privileges ––quick ––routines ––triggers > backup.sql

Step 2

Stop mysql server with

# service mysqld stop

or

# /etc/init.d/mysqld stop

Step 3

Backup complete mysql data dir (/var/lib/mysql).

# cd /var/lib/
# mv mysql mysql_backup
# mkdir mysql
# chown mysql:mysql mysql

Then you should have something like

drwxr-xr-x  5 mysql     mysql     4096 2010-12-30 13:38 mysql

Step 4

Add innodb_file_per_table option in /etc/my.cnf file and save file

Step 5

Re-initialize the database with the following commands

# su mysql
$ mysql_install_db
$ exit

Step 6

Start mysql server with service mysqld start and get into mysql console with

# mysql -u root

Then exec next commands

SET FOREIGN_KEY_CHECKS=0;
SOURCE backup.sql;
SET FOREIGN_KEY_CHECKS=1;

Step 7

Restart mysql server with service mysqld restart

That should be all.

In case that something goes wrong you still have mysql_backup dir which contains all you databases and files. Simple rename the new mysql dir to mysql_new and mysql_backup to mysql. Then restart mysql.

Keep in mind that this operation will kill all services who depends on mysql. So, be quick 🙂

Also, good idea is to execute command mysql_secure_installation which will “tight” your MySQL server.

Manual MySQL update on RHEL/CentOS

If you’re using RH based distros, you’ll probably notice that their habbit is to keep the same software versions in one release.  For example, if you need PHP on CentOS 5.x, # yum install php will install PHP 5.1.6  (Latest PHP version available on http://php.net is 5.3.x).  If you need MySQL, you can count on MySQL 5.0.xx branch and any other wishes will force you to use independent repos (like http://www.jasonlitka.com/yum-repository/). Another option is to use  “do-it-yourself” method.

In this post I’ll write about manual upgrade steps from rpm archives. Please keep in mind that this procedure works for me and please do not send me the private messages. I can’t help you on that way. Only options is to post your comments here and I will try to solve your problem(s).

First thing you need to do is to see what mysql packages do you have installed

# rpm -qa | grep -i ^mysql

You will get something like

mysql-connector-odbc-3.51.12-2.2
mysql-5.0.77-4.el5_5.3
MySQL-python-1.2.1-1
mysql-server-5.0.77-4.el5_5.3
mysql-devel-5.0.77-4.el5_5.3
mysql-bench-5.0.77-4.el5_5.3

Then backup all your databases, save them on the safe location, protect with alarms, guards, poison dogs, cobras, ninjas, etc…

Download rpms from http://www.mysql.com/downloads/mysql/ (MySQL-client-community-5.1.50-1.rhel5.i386.rpm, MySQL-devel-community-5.1.50-1.rhel5.i386.rpm, MySQL-server-community-5.1.50-1.rhel5.i386.rpm, MySQL-shared-community-5.1.50-1.rhel5.i386.rpm)

Note: if you have 32-bit OS, download i386 rpms, if you have 64bit download x86_64 rpms)

Stop mysql server with

# service mysqld stop

Then remove mysql rpms but with –nodeps option (in case you use yum remove mysql, you will need to reinstall a lot of apps because they will be deleted too)

# rpm -e --nodeps mysql

Repeat the same with other mysql packages (devel, bench, client,…). Then you need to install downloaded rpms with rpm -i mysql… and do it.

It is possible to receive the errors like

ls: /var/lib/mysql/*.err: No such file or directory
ls: /var/lib/mysql/*.err: No such file or directory
ERROR: 1136  Column count doesn't match value count at row 1
100910 10:24:00 [ERROR] Aborting
 
100910 10:24:00 [Note] /usr/sbin/mysqld: Shutdown complete
 
Installation of system tables failed!  Examine the logs in /var/lib/mysql for more information.

This will be fixed later…

Now you need to add .my.cnf file to your root dir so you can execute mysql_upgrade command (note that . before m means that file is hidden). Add next lines inside this file (and don’t forget to replace rootpass with your real mysql root pass)

[client]
user=root
password=rootpass

(chmod .my.cnf to 600 for security reasons)

Then exec next command

# service mysql start
# mysql_upgrade

This will produce the similar output

Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
blabla.table1                            OK
blabla.table2                             OK
blabla.table3                                OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log
Error    : You can't use locks with log tables.
status   : OK
mysql.help_category
error    : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it!
mysql.help_keyword
error    : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it!
mysql.help_relation                                OK
database2.cache
error    : Table upgrade required. Please do "REPAIR TABLE `cache`" or dump/reload to fix it!
database2.contacts                             OK
database2.identities                           OK
database2.messages
error    : Table upgrade required. Please do "REPAIR TABLE `messages`" or dump/reload to fix it!
database2.session
error    : Table upgrade required. Please do "REPAIR TABLE `session`" or dump/reload to fix it!
database2.users
error    : Table upgrade required. Please do "REPAIR TABLE `users`" or dump/reload to fix it!
 
Repairing tables
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_topic                                   OK
mysql.proc                                         OK
mysql.time_zone_name                               OK
database2.cache
note     : The storage engine for the table doesn't support repair
database2.messages
note     : The storage engine for the table doesn't support repair
database2.session
note     : The storage engine for the table doesn't support repair
database2.users
note     : The storage engine for the table doesn't support repair
Running 'mysql_fix_privilege_tables'...
OK

As you can see, the database database2 can’t be repaired and you should drop all tables inside this database and import your backup. After this, you can check is everything ok with mysql_upgrade –force

Once again, restart mysql with service mysql restart and check logs. Test is everything ok, try ti create a new database, optimize your installation, eat something…

Note:
I had a lot of problems with this upgrade. I had to upgrade PHP to 5.3.x, I had to recompile postfix with MySQL support, I had to download and recompile Dovecot because Dovecot from CentOS repos is compiled with mysql 5.0 branch. Some versions of RoundCube doesn’t work with php 5.3.x so you should download latest, etc etc… It can be done but please be careful with this. Who knows which nuclear reactor will explode after this 😛

MySQL Workbench

MySQL Workbench is a cross-platform, visual database design tool developed by MySQL. It is the highly anticipated successor application of the DBDesigner4 project. MySQL Workbench will be available as a native GUI tool on Window, Linux and OS X.

MySQL Workbench 5.2 RC provides:
1. Data Modeling
2. Query (upgrade from MySQL Query Browser)
3. Admin (upgrade from MySQL Administrator)

Please get your copy from this page

HowTo recover MySQL root password

By default, MySQL Server will be installed with root superuser without any password. You can connect to MySQL server as root without requiring password or by keying in blank password. However, if you have set the password for root and forget or unable to recall the password, then you will need to reset the root password for MySQL.

Step 1 is to stop MySQL service with

# /etc/init.d/mysql stop

or

# service mysqld stop

Step 2 is to start MySQL w/o root password

# mysqld_safe --skip-grant-tables &

Step 3 is to connect with mysql client

# mysql -u root

Step 4 is to finally change old root password with

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step 5 is to stop mysql server and restart it without –skip-grant-tables parameter

# /etc/init.d/mysql stop

Step 6 is to start mysql server and test it with

# /etc/init.d/mysql start
# mysql -u root -p

Note:

After you install your new server, it is very good idea to improve the security of your MySQL installation with

# mysql_secure_installation

With mysql_secure_installation wizard you can set a password for root account, you can remove root accounts that are accessible from outside the local host, you can remove anonymous-user accounts and you can remove the test database, which by default can be accessed by anonymous users. The script will prompt you to determine which actions to perform.

SysBench on CentOS – HowTo

If you want to test server performance, you can think about SysBench. SysBench is a modular, cross-platform and multi-threaded benchmark tool for evaluating OS parameters that are important for a system running a database under intensive load. The idea of this benchmark suite is to quickly get an impression about system performance without setting up complex database benchmarks or even without installing a database at all.

Current features allow to test the following system parameters:

* file I/O performance
* scheduler performance
* memory allocation and transfer speed
* POSIX threads implementation performance
* database server performance (OLTP benchmark)
(Primarily written for MySQL server benchmarking, SysBench will be further extended to support multiple database backends, distributed benchmarks and third-party plug-in modules)

I couldn’t find CentOS RPM so here are few tips how to install it manually.

Download Sysbench (current version is 0.4.12)

# wget http://garr.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.12.tar.gz

Then unpack it and install with

# tar -xvzf sysbench-0.4.12.tar.gz
# cd sysbench-0.4.12
# libtoolize --force --copy 
# ./autogen.sh
# ./configure
# make
# make install

To test CPU performance you can try

# sysbench --test=cpu --cpu-max-prime=20000 run

For MySQL test, you’ll need to prepare database for testing with

# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=500000 --mysql-user=test_database --mysql-password=test_database_password --mysql-socket=/var/lib/mysql/mysql.sock prepare

(replace test_database with valid username and test_database_password with valid password)

This command will create sample table inside test_database and it will have 500 000 rows (InnoDB engine).

sysbench 0.4.12:  multi-threaded system evaluation benchmark
 
No DB drivers specified, using mysql
Creating table 'test-database'...
Creating 500000 records in table 'test-database'...

Now to start read test

# sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=500000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-read-only --mysql-user=test_database --mysql-password=test_database_password run

For read-write test you can try

# sysbench --num-threads=16 --max-requests=10000 --test=oltp --oltp-table-size=500000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-user=test_database --mysql-password=test_database_password run

More info about specific parameters can be found in official docs (http://sysbench.sourceforge.net/docs/)

Basic MySQL operations I

In this post, I will present few basic commands for MySQL administration. You’ll see how to create a database, create user, assign a database for specific user, etc. First I suppose that you have root password set and that you know it… (in next posts I will present few steps to set this password or to recover it…)

Log in to mysql console:

 
[root@hydra mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 248
Server version: 5.0.45 Source distribution
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Here you can see server version (in this case 5.0.45). With next command you can see all databases

 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| birds              |
| mysql              |
| private             |
| bdd1             |
| test               |
+--------------------+
6 rows in set (0.05 sec)
 
mysql>

If you want to create a new database, enter next command (remember that all SQL commands must be terminated with ; )

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

Now we need to assign a user to newly created database. Also, we need to grant him all privileges for this database but restrict access and operations on another databases (except test database)

mysql> CREATE USER 'db1'@'localhost' IDENTIFIED BY 'db1pass';
Query OK, 0 rows affected (0.02 sec)
 
mysql> GRANT ALL PRIVILEGES ON db1.* TO 'db1'@'localhost' IDENTIFIED BY 'db1pass';
Query OK, 0 rows affected (0.01 sec)
 
mysql> quit;
Bye

After this you can repeat step from the beginning and test new user account. In next posts I will present phpMyadmin