setting cookies in php | jquery autocomplete example | jquery autocomplete in input
 


 

MySQL in a Nutshell



MySQL is "The world's most popular open source database". It is easy to setup MySQL either in Linux or Windows. Installation procedure creates the first user root and applies basic configuration to current database server.

There are many database managers with user friendly graphical or web based interface, useful to perform basic or advanced MySQL administration tasks. However, these applications are not always available (for example in production server environment). MySQL (as any other database server) offers mysql client and the utility mysqldump, which are command line tools, useful to manage our MySQL server.

Here are the most important tasks for everyday use, every administrator or web developer needs to know.

Connect

Localhost

	mysql -u username -p

Remote host

	mysql -u username -p -h hostname

REMARK: in order to increase security, do not provide your password (e.g. mysql -u username -p[password]). Wait the system to prompt for the password.

Create database

We suppose that InnoDb is default in server installation and utf8 is default database collation.

	mysql -u username -p
	create database dbname collate utf8_general_ci;

"_ci" means case insensitive.

Delete database

Use the following commands:

	mysql -u username -p
	drop database dbname;

Create user

Use the following commands:

	mysql -u username -p
	create user 'username'@'hostname' identified by 'password';
	grant all privileges on dbname.* to 'username'@'hostname';
	flush privileges;

hostname = localhost or remote host or IP

Set or change user password

Use the following command:

	mysql -u username -p
	set password=password('password');

Export database to SQL

Export entire database

	mysqldump -u username -p dbname > /path/to/db.sql

Export only schema

	mysqldump -u username -p --no-data dbname > /path/to/db_structure.sql

Export only data

	mysqldump -u username -p --no-create-info dbname > /path/to/db_data.sql

Export a table

	mysqldump -u username -p dbname tablename > /path/to/db.sql

Import database from SQL

From Shell

	mysql -u username -p < /path/to/db.sql

From client environment

	mysql -u username -p
	use dbname;
	set names utf8;
	source /path/to/db.sql

Execute query from sql file

Use the following command:

	mysql -u username -p < /path/to/query.sql

Database objects

List all MySQL databases

	mysql -u username -p
	show databases;

List tables in MySQL database

	mysql -u username -p
	use dbname;
	show tables;

View table structure

	mysql -u username -p
	use dbname;
	describe tablename;

Exit client

Exit client and return to Shell:

	exit;
Εγγραφή!
Sign-up for our free email newsletter. Get updates when new tutorials and tips are published. You can unsubscribe anytime with a click.

Your comments are welcomed!

This site actively encourages commenting on any post. Comments are not pre-moderated, but this community does not tolerate direct or indirect attacks, name-calling or insults. Please, read terms of use and Comment Policy at privacy policy.

10 Web Development Blogs You Should Follow How to Write Code for Any Database with PHP ADODB