This articles assumes you installed mysql via compiling (as in Tutorial 3), or that it came installed on your distro. If mysql is not installed, check your distros documentation for how to do this.
First let's make sure mysql is running.
mysql -u root -pPASSWORD
Remember, there is no space between -p and PASSWORD.
If you get an error at this point, you might not have a password set for root. Let's do that.
mysqladmin -u root password new-password
Now let's go over some basic tasks.
Creating/Deleting MySQL Databases
create database mydatabase;
drop database mydatabase;
Allowing users to access their own mysql database(s)
This allows a user to access all tables in a certain database, and gives them full access to it. You need the second line in order to update what you just did.
grant all privileges on mydatabase.* to username@localhost identified by 'password';
flush privileges;
Restoring a backup database
Sometimes you'll come across a file filled with mysql information that you need to add. In this example, the file mentioned is mysql.dat
mysql -u root -pPASSWORD < mysq.dat
Viewing MySQL Databases
mysql -u root -pPASSWORD
mysql>
mysql> show databases;
+-----------+
| Database |
+-----------+
| database1 |
+-----------+
1 row in set (0.00 sec)
mysql>
mysql> use database1;
mysql> show tables;
+---------------------+
| Tables_in_salesdata |
+---------------------+
| test |
+---------------------+
1 row in set (0.00 sec)
mysql>
mysql> describe test;
+---------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+------------+----------------+
| num | int(11) | | PRI | NULL | auto_increment |
| date_modified | date | | MUL | 0000-00-00 | |
| name | varchar(50) | | MUL | | |
| description | varchar(75) | YES | | NULL | |
+---------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)
mysql> select * from test limit 1;
Since this is a new database, you won't get anything. One you start adding information, it would show the contents of the table.
Lost your password?
/etc/init.d/mysql stop
If this doesn't work, /etc/rc.d/rc.mysqld stop might work.
safe_mysqld --skip-grant-tables &
mysqladmin -u root flush-privileges \
password "ack33nsaltf1sh"
Now restart mysql normally.
/etc/init.d/mysql restart
Backing-up/Restoring MySQL Databases
Backup:
mysqldump --add-drop-table -u root -pPASSWORD mydatabase > /tmp/mydatabase.dat
Restore:
mysql -u root -pPASSWORD mydatabase < /tmp/mysqldatabase.dat