MySQL with Ubuntu

references

• Switch to UTF-8 charset in Mysql on Ubuntu
• Changing default storage engine in MySQL
• Finding out the encoding of a MySQL database
• How to change the MySQL data default directory

utf-8

Add a new configuration file.

user$host:~$ sudo cat /etc/mysql/conf.d/default_character_set_as_utf8.cnf
[mysqld]
default-character-set=utf8
[client]
default-character-set=utf8
user@host:~$ 

Or

user$host:~$ sudo cat /etc/mysql/conf.d/default_character_set_as_utf8.cnf
[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8
[client]
default-character-set=utf8
user@host:~$ 

Restart MySQL.

user@host:~$ sudo service mysql restart
mysql start/running, process xxxx
user@host:~$ 

Check it out.

user@host:~$ mysql -u root -p -e "show variables like '%character%'"
Enter password: 
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
user@host:~$ 

innodb

Add a new configuration file.

user@host:~$ sudo cat /etc/mysql/conf.d/default_storage_engine_as_innodb.cnf
[mysqld]
default-storage-engine = innodb
user@host:~$ 

Restart MySQL.

user@host:~$ sudo /usr/sbin/service mysql restart
mysql start/running, process xxxx
user@host:~$ 

Check it out.

user@host:~$ mysql -u root -p -e "show engines"
Enter password: 
...
what the f...?
...
user@host:~$ 

Too long?

user@host:~$ mysql -u root -p -e "select engine,support from information_schema.engines"
Enter password: 
+------------+---------+
| engine     | support |
+------------+---------+
| InnoDB     | DEFAULT |
| MRG_MYISAM | YES     |
| BLACKHOLE  | YES     |
| CSV        | YES     |
| MEMORY     | YES     |
| FEDERATED  | NO      |
| ARCHIVE    | YES     |
| MyISAM     | YES     |
+------------+---------+
user@host:~$ 

datadir

Stop MySQL.

user@host:~$ sudo service mysql stop
mysql stop/waiting
user@host:~$ 

Copy all files to new datadir.

user@host:~$ sudo cp -R /var/lib/mysql/* /path/to/new/datadir/
user@host:~$ sudo chown -R mysql:mysql /path/to/new/datadir
user@host:~$ 

Change MySQL configuration.

user@host:~$ cat /etc/mysql/my.cnf | grep datadir
#datadir        = /var/lib/mysql
datadir         = /path/to/new/datadir
user@host:~$ 

My Cherry Amour.

user@host:~$ cat /etc/apparmor.d/usr.sbin.mysqld
...
  #/var/lib/mysql/ r,
  /path/to/new/datadir/ r,
  #/var/lib/mysql/** rwk,
  /path/to/new/datadir/** rwk,
...
user@host:~$ sudo /etc/init.d/apparmor reload
 * Reloading AppArmor profiles                              [ OK ]
user@host:~$ 

Start MySQL.

user@host:~$ sudo service mysql start
mysql start/running, process xxxx
user@host:~$