MySQL Commands With Examples-Permanent
The following MySQL Commands were originally split into several smaller blog posts that I had built up over the years, I have now consolidated the articles into a single post (feel free to link to this resource from your site).Please note this article contains commands & examples for the mysql command line client, it does not contain information for phpMyadmin or similar GUI based software.
MySQL Set Root Password
By default MySQL has no password set, this might be fine for a private development environment but unacceptable for production servers. You can set the mysql root password various ways but below is a nice simple method that works:
1mysqladmin -u root password YOURNEWPASSWORDSet / Change MySQL Users Passwords from the Linux Shell
1mysqladmin -u username -h your-mysql-host -p password 'newpassword' You should now be able to restart MySQL and login with your new root password.
How To Connect to MySQL
To connect to your local MySQL server from the command line enter:
1mysql -u root -p If you need to login to a remote MySQL server, you cn either SSH to the server and login or use the following commnd (if the server allows external connections):
1mysql -h hostname -u root -pMySQL Create Database
The following command will create a new MySQL database:
1create database example_db;Backup a MySQL Database using mysqldump
Backing up a MySQL database to a flat file is refered to as “dumping the database”, there are several ways to acomplish this taske here are a few of the methods I use.
Basic mysqldump to a .sql file:
1mysqldump -u root -p database-name > /tmp/database-backup.sql You can also dump the database and compress on the fly by piping it through gzip:
1mysqldump -u root -p database-name | gzip -v > database-backup.sql.gzMysqldump a remote database & transfer over SSH using gzip compression
Note you should execute the following command on the remote server that is currently serving the database, so you are affectivly pushing the db to your local machine.
Mysqldump a remote mysql database to your local machine using SSH & gzip compression (a fast way of taking a backup of a remote database). :
1mysqldump -u root -p database-name | gzip -c | ssh user@your-local-machine 'cat > /tmp/database-backup.sql.gz'Dump all MySQL Databases on a server
If you wish to dump all databses on a server to a single dump file enter:
1mysqldump -u root -p your-root-password --opt >/tmp/databases.sqlMysqldump & Skip Table(s)
While carrying out a nasty phpBB migration I was>1mysqldump: Error 1194: Table 'phpbb_sessions' is marked as crashed and should be repaired when dumping table `phpbb_sessions` at row: 37 71.0% The best option you have if you need to take a backup in it’s current state is to tell mysqldump to skip the tables with:
1mysqldump -u username -p your-database --ignore-table=your-database.broken-table > your-database.sql If you need to skip more than one table you can just add multiple, example below:
1mysqldump -u username -p your-database --ignore-table=your-database.broken-table --ignore-table=your-database.broken-table2 > your-database.sql Once you have a backup I would recommend repairing the tables.
Dump a specific table from a mysql database
1mysqldump -c -u username -p your-pass database-name table-name > /tmp/db-name.table-name.sqlImport a MySQL Database
Simple mysql db import from a .sql file:
1mysql -u username -p -h localhost database-name < database-backup.sql Import a mysql database from .sql.gz
1zcat database-backup.sql.gz | mysql -u root -p database-name Import a .sql file from the mysql command line (you can se the output on the console as it imports, handy if your getting an import error from mysql), first select the mysql database you wish to import into and run:
1source ./db-backup.sqlSelect a Database in MySQL
How to select a database in mysql:
1user database-name;Show Tabels in a Database
First select the databse you wish to use and run the following to show tables in a mysql databse:
1show tables;Create MySQL User
The following example creates a MySQL user called “jesus” with the password “jedimaster”:
1grant usage on *.* to jesus@localhost identified by 'jedimaster'; Next you need to grant the user permission to access your database:
1grant all privileges on heaven_db.* to jesus@localhost The above will allow permission for the user “jesus” on the database “heaven_db”.
If you want “jesus” to have access to all databases on the server you would enter:
1grant all privileges on *.* to jesus@localhost;Show MySQL Database>
The simple way is to use the filesystem to show the mysql database>1cd /var/lib/mysql && ls -lh
If you need to find out the>1SELECT table_schema "Database-Name", SUM( data_length + index_length) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;List MySQL Databases
The following will list all mysql databases on a server:
1show databases; This will give you an output similar to:
1234567891011+--------------------+| Database |+--------------------+| mysql
|| snort_log || squirrelmail || ssweb || test
|| wikidb |+--------------------+13 rows in set (0.07 sec)
Drop A MySQL Database (deletes a db)
The following will drop a databases, when you drop a database you are deleting it. Be careful with this command…
1drop database db-name;Drop a MySQL Table
The following will delete (drop) a mysql table, you need to select the database you wish to use first.
1drop table table-name;How To Reset the MySQL root password
The following proccess will allow you to reset the mysql root password:
Stop mysql:
1/etc/init.d/mysqld stop Start mysql in safe mode:
1mysqld_safe --skip-grant-tables & Login as root:
1mysql -u root Set the mysql root password:
use mysql;update user set password=PASSWORD("new-root-passwd")
where user='root';flush privileges;quit
Restart the mysql service and you can login with your new password:
1/etc/init.d/mysql restartCreate a MySQL table
Here is the basic create table syntax for mysql:
1234CREATE TABLE example (id INT,data VARCHAR(100)); Here is a more complex example:
1 CREATE TABLE table-name (firstname VARCHAR(20), middleinitial VARCHAR(3),
lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),
username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),
datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create an INNODB Table in MySQL
The following will create an innodb table:
1234 CREATE TABLE your_table_name_innodb ( id INT, data VARCHAR(100) ) TYPE=innodb;Convert MyISAM to INNODB
It goes without saying, backup up your db first before running such a task, but here is the mysql syntax to convert a MyISAM table to INNODB:
1ALTER TABLE ENGINE=INNODB;Repair Broken Table(s) in MySQL
If you have a a corrupt / broken table (pretty common with MyiSAM) then take a dump (see the skip broken table with mysqldump instructions above) and then run:
1repair table broke_table_name;Show MySQL Database Fields & Field Formats
1describe table-name;MySQL Show Table Data (Displays the contents of a table)
1SELECT * FROM table-name;Show Columns in a MySQL Table
1show columns from table-name;Add a new column in MySQL
The following is an example of how to add a new column in mysql:
1alter table table-name add column new-column varchar (20);Delete a Column in MySQL
The following is an example of how to delete (drop) a column in mysql:
1alter table table-name drop column column-name;Delete a Row from a field
How to delete a row:
1DELETE from table-name where field-name = 'darth-vader';Show How Many Rows in a MySQL Table
1SELECT COUNT(*) FROM table-name;MySQL Join Tables
How to join tables in MySQL:
1SELECT column_names FROM table-1, table-2 WHERE (table-1.column = table-2.column);MySQL SUM Column Example
1SELECT SUM(*) FROM table-name;Show MySQL & List in Descending Order (DESC)
Show records from col6 and col5 and sort in a descending order using col6:
1SELECT col6,col5 FROM table-name ORDER BY col6 DESC;MySQL Show Records & List in Ascending Order (MySQL ASC)
Show records from col6 and col5 and sort in a ascending order using col6:
1SELECT col6,col5 FROM table-name ORDER BY col6 ASC;MySQL Show Unique Records
Shows all unique records from a mysql table:
1SELECT DISTINCT column-name FROM table-name;Search MySQL Records using a Regular Expression
This regular expression example will show you how to search for MySQL records using regular expressions and the REGXP Binary, the following example will return all results beging with the lower case letter z.
1SELECT * FROM table-name WHERE rec RLIKE "^z";Show Rows Containing a Value
This example will show all rows containing “jesus”:
1SELECT * FROM table-name WHERE field-name = "jesus";MySQL Search for a Record Matching (Various Examples)
Search for records with the name “Jesus” born in “1984”:
1SELECT * FROM table-name WHERE name = "Jesus" AND year = '1984'; Search for anyone called “Jesus” with the phone number “911”
1SELECT * FROM table-name WHERE name = "Jesus" AND year = '1984'; Search MySQL for any records matching the name “Jesus” with the phone number “911” and sort by phone number:
1SELECT * FROM table-name WHERE name != "Jesus" AND phone_number = '911'
order by phone_number;
Show all records starting with “Jesus” and the phone number “911”:
1SELECT * FROM table-name WHERE name like "Jesus%" AND phone_number = '911'; Do the same as about but only show records 1 to 10:
1SELECT * FROM WHERE name like Dave%" AND phone_number = '911' limit 1,10; Feel free to link to this resource from your blog, if you have any suggestions for additional commands please drop me a comment below and I will amend the post.
页:
[1]