Archive for the ‘MySQL’ Category

Changing MySQL data directory CentOS 7

January 14, 2016 Leave a comment

Doing this on CentOS 7 64 bit and MySQL 5.6 community edition

Sometimes it is better to put mysql in a separate partition than its regular location

Typically the mysql database are located in /var/lib/mysql

I want to change it to /var/data/mysql 

Modify the paths as required in the below commands

stop mysql

systemctl stop mysqld.service

create new mysql data directory

mkdir /var/data/mysql

modify /etc/my.cnf and point to new data directory – add the client section to the top



copy all files from /var/lib/mysql to the new directory /var/data/mysql

cp -r /var/lib/mysql/* /var/data/mysql

permissions for the new directory

chown -R mysql /var/data/mysql;
chgrp -R mysql /var/data/mysql;
chmod -R g+rw /var/data/mysql;

also modify SELINUX settings to allow mysql to use the different path

# add context and make it permanent 
semanage fcontext -a -s system_u -t mysqld_db_t "/var/data/mysql(/.*)?"
restorecon -Rv /var/data/mysql

start mysql

systemctl start mysqld.service


MySQL should start cleanly.
You can verify the change by creating a test database.
Then go to /var/data/mysql and you should be able to see the new database there

Categories: MySQL Tags: ,

Increasing File Descriptors and Open Files Limit CentOS 7

September 17, 2015 4 comments

PS: See Andy Dyrcz answer in the comments for a better way to do this

Some programs like Apache and MySQL require a higher number of file descriptors.
This is how you can increase that limit for all users in CentOS 7
Commands require root access

# Find the default limit – check the open files line – it will be 1024

sudo ulimit -a

To increase edit nano /etc/sysctl.conf add the below line, save and exit

fs.file-max = 100000

We also need to increase hard and soft limits
Edit /etc/security/limits.conf add the below lines before the #End, save and exit

* soft nproc 65535
 * hard nproc 65535
 * soft nofile 65535
 * hard nofile 65535

Next run the command

sudo sysctl -p

for MySQL, edit /usr/lib/systemd/system/mysqld.service  and add the below 2 lines at the end, save and exit


then increase the table_open_cache and open_files_limit in my.cnf

# reload systemctl
 sudo systemctl daemon-reload

# if you modified mysql config, restart mysql and check values for table_open_cache and open_files_limit

systemctl restart mysqld.service

run the below command to check the open files limit – change user based on requirement
output should say: open files (-n) 65535

# for mysql
 su - mysql -c 'ulimit -aHS' -s '/bin/bash'

# for apache
 su - apache -c 'ulimit -aHS' -s '/bin/bash'

MySQL command prompt auto-complete

September 25, 2014 1 comment

If you work on the mysql command line, its nice to have an autocomplete similar to the linux bash where you press the TAB key and the commands complete.

In mysql, we can set it up to a degree to provide some hints when we type. Though it might not complete everything you type, it does complete table names etc.

2 ways to do it ( I am on CentOS 6.5 )

when you log into mysql, use the auto-rehash config option like

mysql --auto-rehash -u root -p

if this does not work, then try creating a file called .my.cnf in your home directory and put the below into it


Clear Screen for MySQL Command Prompt – Linux

On Linux, if you have logged into mysql on the command prompt  and want to clear all text on screen you can use

mysql> \! clear

\! system-command is the command format

\! tells mysql to pass the command to the system


[naveen@localhost ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
| Database |
| information_schema |
| drupal |
| employees |
| mysql |
| phpmyadmin |
| test |
| zabbix |
7 rows in set (0.00 sec)
mysql> \! clear
Categories: MySQL, Tips and Tricks Tags: ,

MySQL root login without password

January 3, 2014 Leave a comment

You must be on localhost and have Linux root permissions

I had to create a MySQL account for myself on a Linux box. So here it is

# kill the running mysql process
sudo service mysql stop

# start mysql in safe mode and skip grant tables 
sudo mysqld_safe --skip-grant-tables --skip-networking &

# log in with root 
mysql -u root 

# ( optional ) flush privileges, create new user, grant privileges 
CREATE USER 'new-user'@'localhost' IDENTIFIED BY 'password';

# stop mysqld_safe process 
ps -A | grep mysql
sudo kill < mysql-pid-here >

# start mysql normally 
sudo service mysql start
Categories: Linux, MySQL, Tips and Tricks

MySQL multiple instances on Ubuntu

November 10, 2013 35 comments

Production systems must take care of security and tweaking not covered by this post

The post looks long, but you can get another instance of MySQL running in less than 5 mins


  1. OS: Ubuntu 12.04 LTS server edition – up to date
  2. Already has MySQL installed that comes default with 12.04 – you can easily install LAMP with the command tasksel
  3. MySQL Server version: 5.5.34-0ubuntu0.12.04.1 (Ubuntu)
  4. You have OS root privileges
  5. Default MySQL is running on port 3306

What will we do

  1. Set up 2 more MySQL instances on ports 3307 and 3308
  2. Each instance will have their own config files, data directories and log directories

Stopping default MySQL instance

sudo service mysql stop
sudo ps -A | grep mysql

Creating data directories

  • MySQL cannot share data directories, so we need to set up new ones
  • default basedir = /usr, this can be shared across instances
  • default instance port = 3306 and data dir = /var/lib/mysql
  • new instance       port = 3307 and data dir = /var/lib/mysql3307
  • new instance       port = 3308 and data dir = /var/lib/mysql3308
  • MySQL must own data dirs
  • we need to set rules in apparmor to let MySQL access the new dirs
sudo mkdir /var/lib/mysql3307
sudo mkdir /var/lib/mysql3308
sudo chown -R mysql /var/lib/mysql3307
sudo chown -R mysql /var/lib/mysql3308

Creating log directories

  • create separate log dirs for new MySQL instances
  • default log dir = /var/log/mysql
  • new log dir for 3307 = /var/log/mysql/mysql3307
  • new log dir for 3308 = /var/log/mysql/mysql3308
  • log dirs must be owned by MySQL
  • note that /var/log/mysql already has apparmor permissions for MySQL, so any dir under it also has access
sudo mkdir /var/log/mysql/mysql3307
sudo mkdir /var/log/mysql/mysql3308
sudo chown -R mysql /var/log/mysql/mysql3307
sudo chown -R mysql /var/log/mysql/mysql3308

Creating config files

  • create the config files for new instances by copying default file
  • default config file = /etc/mysql/my.cnf
  • config file for 3307 = /etc/mysql/my3307.cnf
  • config file for 3308 = /etc/mysql/my3308.cnf
  • see config files on github
  • /etc/mysql/my3307.cnf
  • /etc/mysql/my3308.cnf
  • special care has to be taken so that these values are different
  • datadir
  • server-id
  • all port entries
  • all socket entries
  • all pid-file entries
  • all log file entries, general, error, binary etc
sudo cp /etc/mysql/my.cnf /etc/mysql/my3307.cnf
sudo cp /etc/mysql/my.cnf /etc/mysql/my3308.cnf

Apparmor settings ( skip if you dont have this )

  • apparmor is like an application firewall – comes installed default with Ubuntu server
  • command aa-status will show you if it is loaded
  • default apparmor profile for MySQL = /etc/apparmor.d/usr.sbin.mysqld
  • put additional rules in /etc/apparmor.d/local/usr.sbin.mysqld
  • specify the correct data dirs, pid and socket files for each instance – see file on github
  • /etc/apparmor.d/local/usr.sbin.mysqld
  • after modifying, restart apparmor
sudo service apparmor reload

Installing new MySQL instances

  • install MySQL files into the new data dirs for port 3307 and port 3308
  • after this, under each new data dir, you will see the mysql, performance_schema and test dirs
  • this will install MySQL with default settings,  no root password
  • in the below commands, you can use the – -verbose flag to see more details
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql3307 --defaults-file=/etc/mysql/my3307.cnf
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql3308 --defaults-file=/etc/mysql/my3308.cnf

Starting the mysql instances

  • start the default instance on 3306
  • start instances on 3307 and 3308 in the background
sudo service mysql start
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql

Accessing the new instances

  • Note that the new instances on 3307 and 3308 will not have a root password
  • it is important to specify host and host=
  • if host is omitted, or localhost is given, then default instance is assumed ( on 3306 )
  • remember to explicitly specify host and port for all commands
mysql -h --port=3307 -u root
mysql -h --port=3308 -u root

Shutting down the MySQL instances

  • We will use mysqladmin to cleanly shutdown
  • it is important to specify host and and port
  • no password for now
mysqladmin -h --port=3307 -u root shutdown
mysqladmin -h --port=3308 -u root shutdown

Post installation set up ( for each instance )

  • update root password
  • drop all anonymous users – check for users with empty username
  • drop database test
  • flush privileges

update mysql.user set password=PASSWORD('myRootPassword') where User='root';
drop database test;
drop user ''@'localhost';
drop user ''@'%';
drop user ''@'ubuntu';
flush privileges;

Starting new instances on boot and reboot

  • Put commands in the file /etc/rc.local to start new instances on boot
  • the rc.local file will look like this
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql
exit 0


  • Most of the time, the problem is due to incorrect permissions, or incorrect config files or apparmor
  • Check error logs in /var/log/mysql for each instance
  • Make sure that each mysql config has different values for variables
  • Make sure that directory permissions are correct, mysql must own data and log dirs
  • remember to specify host and port explicitly when connecting
  • if connecting from a remote host, check the bind-address config variable in the config file for the instance
  • if connecting from remote host, make sure that ports 3307 and 3308 are open and no other applications are using them
  • Make sure that all dirs have the apparmor permissions and you have reloaded apparmor.
  • You can see enties like the below in /var/log/syslog if apparmor is blocking mysql
Nov 7 11:51:16 ubuntu kernel: [ 1080.756609] type=1400 audit(1383843076.476:32): apparmor="DENIED" operation="mknod"
parent=2749 profile="/usr/sbin/mysqld" name="/var/lib/mysql1/ibdata1" pid=3559 comm="mysqld" requested_mask="c" 
denied_mask="c" fsuid=102 ouid=102

Finally – bash aliases if you need

  • To make commands simpler to type, you can set up bash aliases
  • Put the below aliases or whatever you want, in a file called .bash_aliases in your home dir
  • If the file is not found, create it in ~/.bash_aliases
  • remember to open a new shell for these commands to take effect
########### mysql @ 3307 ##############################
alias mysql3307-start='sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql'
alias mysql3307-stop='mysqladmin -h --port=3307 -u root shutdown -p'
alias mysql3307-root='mysql -h --port=3307 -u root -p'
########### mysql @ 3308 ##############################
alias mysql3308-start='sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql'
alias mysql3308-stop='mysqladmin -h --port=3308 -u root shutdown -p'
alias mysql3308-root='mysql -h --port=3308 -u root -p'
Categories: MySQL

MySQL convert to utf8

A quick way to convert your database to utf8

# Dump the old database as latin1, mysqldump defaults to utf8
mysqldump -h host -u user -p --default-character-set=latin1 old_db > dump.sql

# Rewrite the dump to say 'utf8' and 'utf8_general_ci' 
sed -e 's/SET NAMES latin1/SET NAMES utf8/g' -i dump.sql
sed -e 's/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/g' -i dump.sql
sed -e 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/g' -i dump.sql

# Make sure your new database has the correct character set and collation ( i.e utf8 )
# Import the converted database dump into MySQL.
mysql -h host -u user -p --default-character-set=utf8 new_db < dump.sql

If you want to convert only a certain column – first convert the column to binary and then convert to utf8

update table set column = CONVERT( (CAST(column) AS BINARY ) USING utf8 );

It is also good to make everything to utf8 in your my.ini file

init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'

Categories: MySQL