Start server (Linux):

% /sbin/service mysqld restart

Stop server (Linux):

% /sbin/service mysqld stop

Update /etc/hosts to have all the appropriate hosts entries (all the important hosts in your network), otherwise, you cannot connect from a remote host. Set root password to access MySQL server:

% mysqladmin -u root password mypasswdhere

Access MySQL server as root:

% mysql -u root -p

Create a database:

 mysql> CREATE DATABASE myappdb;

View existing databases:

mysql> SHOW DATABASES;

View existing tables in a database called myappdb:

mysql> USE myappdb;
mysql> SHOW TABLES;

Grant permissions:

 mysql> GRANT ALL ON myappdb.* TO dbuser@localhost IDENTIFIED BY 'myuserpass';

If trying to access from a remote host (i.e. something other than localhost), make sure that that host's FQDN is in the /etc/hosts file. Otherwise you will get “Connection Refused.” For example:

mysql> grant all on *.* to root@192.168.0.62 identified by 'somepassword';

Delete a database called myappdb:

mysql> DROP DATABASE myappdb;

Execute a script containing SQL statements:

mysql> SOURCE myscriptname.sql;

Create a table alumni in database aucdb:

mysql> CREATE TABLE `alumni` (
-> `id` TINYINT (3) UNSIGNED DEFAULT '0' AUTO_INCREMENT,
-> `name` VARCHAR (20),
-> PRIMARY KEY(`id`));

Describe a table:

mysql> DESCRIBE alumni;

Delete a table:

mysql> DROP TABLE alumni;

Execute an SQL statement:

mysql> SELECT * FROM alumni;

Insert records to table:

mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Load data from external source:

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

Delete records:

mysql> DELETE FROM alumni WHERE id=999;

Update records:

mysql> UPDATE alumni SET name='John' WHERE id=999;

• Comparison of SQL Syntax

Use any of these methods:

1. Copy all the files in /var/lib/mysql/* to some backup directory (Linux). Make sure to copy all ib* files (e.g. ibdata1, and optionally ib_logfile0 and ib_logfile1) if using InnoDB tables:

server1% cp /var/lib/mysql/* /data/backup/mysql
server2% cp -r /data/backup/mysql /var/lib
server2% chown -R mysql:mysql /var/lib/mysql 
server2% mysql_upgrade -u root -p --force
Restart mysql server

2. Copy data using mysqldump:

server1% mysqldump > dbase.mysql
server2% mysqldump < dbase.mysql

For backup database
% mysqldump -u root -pPassword -P3309 --routines --no-data originaldb > "d:\dbwithnodata.sql"

For restoration of database
% mysql -u root -pPassword -P3309 newdb < "d:\dbwithnodata.sql"

Alternatively, duplicate database in same server:
% mysqldump -u user -pMyPass -d originaldb | mysql -u user -pMyPass -Dnewdb
The new database must exist already. The -d flag in the ''mysqldump'' command prevents copying of data.

3. Archive and compress the data (Linux):

% tar -czvf /data/backup/mysql-data-backup.tar.gz /var/lib/mysql

4. Better yet, use phpMyAdmin to backup the database.

The procedure under Windows ¹⁾:

• Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager: Start Menu → Control Panel → Administrative Tools → Services. Then find the MySQL service in the list, and stop it. If your server is not running as a service, you may need to use the Task Manager to force it to stop.
• Create a text file (for example C:\mysql-init.txt.) and place the following command within it on a single line:

  SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

• Reset password: At the DOS command prompt, execute this command (NOTE: make sure the filename in the parameter uses the full path!):

  C:\> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt

• Stop the MySQL server, then restart it in normal mode again. If you run the server as a service, start it from the Windows Services window. If you start the server manually, use whatever command you normally use.
• You should be able to connect using the new password.

Run these commands:

$ service mysql stop
$ mysqld_safe --skip-grant-tables &
$ mysql -u root
 
mysql> use mysql;
mysql> update user set authentication_string=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
 
$ service mysql stop
$ service mysql start
$ mysql -u root -p

NOTE: Note: prior to MySQL 5.7 the column was called password instead of authentication_string.

• Unpack the distribution file in your webserver's document root:

  $ cd /var/www/html/support
  $ tar -xzvf phpMyAdmin_x.x.x.tar.gz 

• Create the file config.inc.php. You can use setup script provided in distribution (scripts/setup.php) to create basics of config file. See Setup chapter for details. If you don't like setup or want to fine tune resulting configuration, open (or create in case of starting from scratch) config.inc.php in your favorite editor and fill in there values for host, user, password and authentication mode to fit your environment. Edit the following fields if simply using a default file phpmyadmin/config.inc.php:

  $cfg['PmaAbsoluteUri']  = 'http://www.your-host.com/yourdirectory/';
  $cfg['blowfish_secret'] = 'MySecretPhraseHere';
  
  $cfg['Servers'][$i]['host']      = 'localhost'; // MySQL hostname or IP address
  $cfg['Servers'][$i]['port']      = '';          // MySQL port - leave blank for default port
  $cfg['Servers'][$i]['auth_type'] = 'cookie';    // Authentication method (config, http or cookie based)?
  $cfg['Servers'][$i]['user']      = 'root';      // MySQL user
  $cfg['Servers'][$i]['password']  = '';          // MySQL password (only needed with 'config' auth_type)

• Open the file <www.your-host.com>/<your-install-dir>/index.php in your browser. phpMyAdmin should now display a welcome screen and your databases, or a login dialog if using HTTP or cookie authentication mode.

• Click on “Databases” link.
• Enter database name and press <Create> button.
• Enter name of table to create and number of fields, then press <Go>.

• Select database (click on database, found on left column).
• Export (click on “Export” tab on right side).
• Select all fields to export.
• Select additional options under “Structure”, if needed: Add DROP TABLE, Add IF NOT EXISTS, etc.
• Select compression: GZipped (automatically selecting “Save as File”).
• Press <Go> button. This downloads a copy to your computer. This is the file to be used in case of database restoration.

• Select database (click on database, found on left column).
• Import structure and data by clicking on SQL tab (right column).
• Browse for location of file with database backup (see “Backup a database”).
• Select compression: GZipped.
• Press <Go> button. This unpacks and runs the SQL statements stored in that gzipped file.
• Include the following statement in an SQL file that has constraints and fails to import:

  SET SESSION FOREIGN_KEY_CHECKS=0;