Image

MySQL / MariaDB

How to create MySQL user

User account can be created by using MySQL’s GRANT statement. For this, you’ll need to login to MySQL using the root account, which has full privilege on the mysql.user table.

The following statement will create a user account named myusername which will have full access to the mydatabase database from localhost and set the password to mypassword

 GRANT ALL ON mydatabase.* TO 'myusername'@'localhost' IDENTIFIED BY 'mypassword';

MySQL configuration files

/etc/mysql/my.cnf, /etc/my.cnf or ~/my.cnf

How to change MySQL data directory

[email protected]:~/Dropbox/Workspace/Apache/mirror/oseems.com$ sudo vi /etc/mysql/my.cnf
[email protected]:~/Dropbox/Workspace/Apache/mirror/oseems.com$ sudo /etc/init.d/apparmor restart
* Reloading AppArmor profiles
Skipping profile in /etc/apparmor.d/disable: usr.bin.firefox
Skipping profile in /etc/apparmor.d/disable: usr.sbin.rsyslogd

 ...done.

[email protected]:~/Dropbox/Workspace/Apache/mirror/oseems.com$ sudo vi /etc/apparmor.d/usr.sbin.mysqld
[email protected]:~/Dropbox/Workspace/Apache/mirror/oseems.com$ sudo /etc/init.d/apparmor restart
* Reloading AppArmor profiles
Skipping profile in /etc/apparmor.d/disable: usr.bin.firefox
Skipping profile in /etc/apparmor.d/disable: usr.sbin.rsyslogd

 ...done.

[email protected]:~/Dropbox/Workspace/Apache/mirror/oseems.com$ sudo /etc/init.d/mysql restart
* Stopping MySQL database server mysqld

 ...done.

* Starting MySQL database server mysqld

 ...done.

* Checking for tables which need an upgrade, are corrupt or were
not closed cleanly.

How to clone MySQL table

If you want to create a new MySQL table with the same structure as an existing table, you can use the following MySQL statement;

CREATE TABLE new_table LIKE source_table;

This will just create a new, empty table and does not copy the following;

  • Foreign key definitions
  • DATA DIRECTORY
  • INDEX DIRECTORY
  • Data

To also copy the data from the source table, use the following INSERT statement after the table is created;

INSERT INTO new_table SELECT * FROM source_table;

How to import CSV files to MySQL table

You can import data from CSV (Comma-Separated Values) files directly to MySQL tables using LOAD DATA statement or by using MySQL’s own mysqlimport tool.

To be able to import the files, you’ll need to be able to figure out the following properties of the CSV files;

  • Line terminator
  • Field terminator
  • Field enclosure

The following example is for importing source.csv with the following content into target_db.target_table;

"aa","ab","ac"
"ba","bb","bc"
"ca","cb","cc"

How to restart MySQL service

Replace mysql with mysqld for RedHat-based platforms.

Ubuntu 16.10 and later, RedHat/CentOS 7 and later, fedora and other platform with systemd

sudo systemctl restart mysql.service

Older platforms with System V init scripts

sudo /etc/init.d/mysql restart

Platforms with service command. Normally a wrapper to System V init scripts or systemd commands.

sudo sudo service mysql restart

How to use case insensitive table name for MySQL in Linux

Files and folders in Linux and Unix in general are case sensitive.

How to reset MySQL root password

MySQL is one of the world’s most popular open source database. It is mainly used on many database enabled website mostly for being free, and powerful.

If you happen to lose your MySQL root password, you can follow these steps to reset it.

Top