Guides:MySQL/MySQL Quick Start Guide
Note: This is just a generic guide to the setup of MySQL. For information on the SQL language, see Category:Guides:SQL.
Generally, we use capital letters for mysql statements, but that is not required. The only time case matters is for the name of fields in a table, or database and table names, and when using \G instead of \g to end a statement. SQL statements are terminated with a semicolon, or a \g. You can also terminate a mysql statement with a \G if you wish an expanded/more human readable output to some commands.
If you ever get a prompt "->" instead of "mysql>", that means mysql is waiting for you to finish your statement. That probably means you failed to provide either a semicolon, a \g, or a \G at the end (note that \G and \g produce responses formatted differently, but basically they have the same effect.
To exit mysql, simply use the quit, exit, or \q statement.
The mysql client
Options to the mysql client are optional, but generally you'll need a few. If you forget to specify "-p" then mysql will try logging in without a password-- which probably wont work.
mysql -h remote-host -u userid -p mysql -h remote-host -u userid --password='password'
If -p is specified, but no password given, then mysql will prompt for a password. Failure to use the -p option for accounts for which passwords are assigned will result in a failure. If -h isn't given, then mysql attempts to connect to localhost.
The mysql server must be running on the local host for mysqladmin to work.
Brackets "" are used to indicate optional parameters-- don't actually type the brackets :-P.
mysqladmin, mysqldump and mysql all take the -u -h and -p flags.
Setting Initial mysql root Password
This may have already been done when MySQL was installed on your system. Versions of Ubuntu 9+ and later prompt for the root password on install, earlier versions do not. If a root password has already been assigned, then skip to the following section on how to reset a forgotten MySQL root password.
First step is to start the server.
mysqladmin method (localhost only)
# mysqladmin -u root password "new-password"
mysql client method
# mysql -u root mysql mysql> use mysql; mysql> SET PASSWORD FOR root=PASSWORD('new password'); mysql> flush privileges; mysql> quit;
You can also set the password for specific remote hosts, by using root@hostname instead of root for the host.
mysqladmin -u user-id "old-password" "new-password"
Resetting forgotten mysql root password
Note: The command "sudo" allows for commands to be executed as the super user on Unix and Unix-like operating systems (including Mac OS X, Linux, BSD, Solaris, and many others). If you're already logged in as root, then you do not need to use sudo. If your system does not have sudo, then you will need to log in as the root or use and account with administrator privileges (for both Unix/Unix-like, and non-Unix systems such as Microsoft Windows).
1.First step is to kill the running mysqld server:
Using the init scripts:
# sudo /etc/init.d/mysqld stop
Using the killalll command:
# sudo killall mysqld
Now start a new server that will ignore password checks...
If your have mysql_safe:
# sudo mysql_safe --skip-grant-tables &
# sudo mysqld --safe-mode --skip-grant-tables &
2.Next, lets change the password(s):
# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update user set password=PASSWORD("root password") -> where User='root'; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye # sudo killall mysqld
3.Finally, restart the server
# sudo /etc/init.d/mysql start
Or you can restart it without using init scripts:
# sudo mysqld&
Addining a Database
mysqladmin create database_name
mysql client method
# mysql -u root -p [password] [-h hostname] mysql> CREATE database database_name; mysql> quit;
Listing databases and tables
If you want to see all your databases:
mysql> SHOW databases;
If you want to take a look at the tables in a database:
mysql> USE database_name; mysql> SHOW tables;
Removing a Database or Table
The DROP command is used for all these operations.
Dropping a database
mysql> DROP DATABASE database_name; quit;
You can use SHOW DATABASES; to list all databases on the mysql server. After selecting a database with USE database_name, you may list all tables within that database with SHOW TABLES. No, the commands do not need to be upper case.
Dropping a table
To drop a table:
mysql> USE database_name; mysql> DROP TABLE table_name;
Backing up a Database
Use mysqldump to back up your database.
mysqldump -u root -p databasename >backup-file.sql
The sql file may be very large, so piping it throug gzip first is a good idea.
mysqldump -u root -p databasename |gzip -9 >backup-file.sql.gz
Restoring a Database
First, you'll need to create the database you wish to restore as it doesn't exist yet.
# mysql -u root -p mysql> CREATE DATABASE us_presidents; quit;
Next, restore your database.
mysql -u root -p databasename <backup-file.sql
gzip -d <backup-file.sql.gz|mysql -u root -p database
You may use bzip2 if you like, but gzip is far faster, and on modern computers, can compress text data faster than it can be written to the hard drive (PATA/IDE drives, fast SATA/SCSI/SCA drives may be another matter).
Adding users is done with the following mysql command:
GRANT privlages ON database_name.tablename TO user@[hostname/'%'] IDENTIFIED BY 'password';
Though this may be more useful if you intent to grant a user control over a database (including tables, or if no tables in the database exist):
GRANT ALL ON database.* TO user@'%' IDENTIFIED BY 'password';
You may use wild cards for the database name. To select all databases, use "*.*" for the database name. Using '%' for the hostname means the user may log in from any host (the '%' wildcard doesn't work on all distributions). There are several privileges that may be set per user. If you're creating a database for your use, you should use ALL for the privileges (optionally, you may add "privileges" after the ALL statement). See the permission list below.
If we wanted to create a user zippy from da-host.com with the password zipzap, with all privileges on the boo database, we would use:
GRANT ALL ON boo.* TO email@example.com IDENTIFIED BY 'zipzap';
We could also do this:
GRANT ALL PRIVILEGES ON boo.* TO firstname.lastname@example.org IDENTIFIED BY 'zipzap';
To create a user with ALL, SUPER, and CREATE USER privileges on all database that can log in from any host computer, using the ID anewroot with password r00tyt00ty, we would use:
GRANT ALL, SUPER, CREATE USER ON *.* TO anewroot@'%' IDENTIFIED BY 'r00tyt00ty';
We could also use *.
Here is a partial list of permissions:
|ALL [PRIVILEGES]|| sets all simple permissions except granting of
|ALTER||allows altering of tables (ALTER TABLE)|
|CREATE||allows creation of new tables (CREATE TABLE)|
|CREATE TEMPORARY TABLES||allows creation of temporary tables (CREATE TEMPORARY TABLE)|
|DROP||allows dropping of tables (DROP TABLE)|
|DELETE||allows use of DELETE (DELETE)|
|INSERT||allows use of INSERT (INSERT)|
|SELECT||allows use of SELECT, see note below (SELECT, SHOW CREATE TABLE)|
|FILE||allows use of file commands (SELECT ___ INTO OUTFILE, LOAD DATA INFILE)|
|INDEX||allows creating and dropping indexes (DROP INDEX, CREATE INDEX)|
|RELOAD||allow use of FLUSH (FLUSH)|
|UPDATE||allows use of UPDATE (UPDATE)|
|PROCESS||allows listing of processes, useful for troubleshooting (SHOW PROCESSLIST)|
|SUPER||special privileges, including making one connection to the server even if max_connections is reached. (CHANGE MASTER, KILL, PURGE MASTER LOGS, SET GLOBAL)|
|SHUTDOWN||allows shutdown of mysql server via mysqladmin|
|USAGE||same as NO PRIVILEGES|
|GRANT OPTION||allows for graning of privlages.|
|EXECUTE||Allows for executing of stored routines on mysql 5+|
|ALTER ROUTINE||allows for altering of stored routines on mysql 5+ (ALTER ROUTINE)|
|CREATE ROUTINE||allows for creating stored routines.|
|REPLICATION CLIENT||allows client get location of master and slaves|
|REPLICATION SLAVE||needed by replication slaves to allow access to binary log file.|
It has been reported that granting SELECT access can allow access to passwords if FEDERATED storage engine is used. This matter is made even worse if the passwords are stored in plain text.
More can be found at http://dev.mysql.com/doc/refman/5.0/en/grant.html
Revoking user privileges
The format of the REVOKE command is similar to GRANT:
REVOKE privilege ON database FROM user@host;
Droppign a user
This is a little different.. use the DELETE command instead.
DELETE FROM user WHERE (user="username"); DELETE FROM user WHERE (user='user1' OR user='user2' OR user='user3' ...);
Getting a list of users
You'll want to save these commands as a script to make it easier to send the output of mysql to a file. This is because the output stream can be quite long when using \G instead of a semicolon or \g to terminate a command.
You could use a semicolon to terminate teh select command, but the output is very hard to read without using the most pager.
To get a list of all users:
use mysql; select * from user\G quit;
To get a list of all users by database:
use mysql; select * from db\G quit;
To get a list of all users for just one database:
SELECT * FROM mysql.db WHERE (Db='database_name')\G quit;
then run mysql with (assuming you saved the script as script.sql):
mysql -u root -p <script.sql >outfile
You could also use grep just to list the user names:
mysql -u root -p <script.sql|grep "User: "