• 1 Installation
• 2 Basic Examples
• 2.1 Add a database
• 2.2 Add a user
• 3 Moving a database
• 3.1 Export the Data
• 3.2 Import the Data
• 4 Useful examples
• 4.1 creating new users
• 4.2 setting passwords
• 4.3 getting info
• 4.4 finding table size

Installation¶

# apt-get install mysql-server
# mysqladmin -u root password 'new-password'

Basic Examples¶

Add a database¶

Using the linux command line:

# mysqladmin create dbname

Using the mysql command line:

# mysql --user root -p
mysql> CREATE DATABASE dbname;

Add a user¶

Add a user and give them access to a database:

> mysql --user root -p 
mysql> use mydatabase;
mysql> grant all on mydatabase.* to myuser@localhost identified by 'your_password';
mysql> flush privileges;
mysql> quit

Moving a database¶

Export the Data¶

There are lots of ways of creating a data file for export. Here are some useful examples:

Standard dump, including create database commands:

mysqldump --user=root --password={rootpw} --quick --databases {dbname} > data.sql

Some other options (without the --databases there will be no CREATE DATABASE):

mysqldump --host={hostname} --port=3306 --complete-insert --lock-tables {database} > data.sql 

Import the Data¶

mysql -u root -p < data.sql

you may have to DROP DATABASE mydb first, and then CREATE DATABASE mydb.

Useful examples¶

dirac.org/linux/databases/mysql

creating new users¶

see Adding New User Accounts to MySQL

create a user, set their password, and assign access permissions in one command:

GRANT USAGE ON dbname.* TO username@localhost IDENTIFIED BY 'password';

Alternately:

GRANT ALL ON dbname.* TO username@localhost IDENTIFIED BY 'password';

If this is not the first time the user was created:

FLUSH PRIVILEGES

setting passwords¶

Set initial root password:

$ mysqladmin -u root password NEWPASSWORD

Change root password:

$ mysqladmin -u root -p oldpassword newpass

Change password for a user:

$ mysqladmin -u joe -p oldpassword newpass

getting info¶

show grants for redmine;

finding table size¶

SELECT table_name,(data_length+index_length)/power(1024,2) as tablesize_mb FROM information_schema.tables order by tablesize_mb;