MySQL

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;