How to Work with MySQL – Basic Tasks

This material is tested with MySQL 5.1 and 5.5 on various Windows versions.

This article covers these topics:

-010- MySQL Data Location
-020- Shortcut to MySQL Command Shell
-030- Create a New Database
-035- Delete an Existing Database
-040- Display a List of All DB Users
-050- Display a List of All Tables
-060- List All Fields in a Table
-070- Free ODBC Connector is Available
-080- Create a New User
-085- Change User Password
-090- Delete a User
-100- Grant User Rights to a D/B and Show It
-110- Save Database into a Dump File
-120- Restore Database From a Dump File
-130- Rename Database
-140- Backup Database
-150- Automate Backup and Save Last 10 days
-170- Create a table with FULLTEXT index
-180- Move several tables from one server to another

Remember to use semi-column at the end of each commands.
For some reason command USE DB_NAME doesn’t require a semi-column.

`

-010- MySQL Data Location

Your MySQL data is stored in
C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data
Can I move it? -Yes – See related article in this category (http://www.domainwebcenter.com/?p=72)

`

-020-Shortcut to MySQL Command Shell

Create a shortcut to start your MySQL shell:

“C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe” -u root -p
“%ProgramFiles%\MySQL\MySQL Server 5.1\bin\mysql.exe” -u root -pYourPassword

If you type your MySQL password immediately after switch-p (no spaces), you can get directly into the shell without need to type the password every time.

To exit your MySQL command prompt use \q

`

-030- Create a New Database

CREATE DATABASE YourDB;
`

-035- Delete an Existing Database

DROP DATABASE YourDB;

`

-040- Display List of All DB Users

SELECT user FROM mysql.user;

`

-050- Display a list of all tables

use YourDB;
show tables;
show tables like "bb%";

This last example lists all tables that starts with letters “bb”.

`

-060- List All Fields in a Table

SHOW databases;
SHOW tables FROM YourDB;
SHOW columns FROM Table_Name;

`

-070- Free ODBC Connector is Available

Get a free ODBC driver from MySQL.com
You can use it for browse you data in MS Access

`

-080- Create a New DB User

CREATE USER UserName@localhost IDENTIFIED BY ‘UserPass’;

`

-085- Change DB User Password

SET PASSWORD FOR DB_User@localhost = PASSWORD(‘UserPassword’);

`

-090- To delete a DB User

DROP USER YourUser;
DROP USER YourUser@localhost;

`

-100- Grant user rights to a D/B and Show It

use DB_Name
GRANT ALL ON YourDB TO YourUser;
GRANT ALL ON YourDB.* TO “DB_User”@”localhost”;
GRANT ALL ON YourDB.* TO DB_User@localhost;
FLUSH PRIVILEGES;
SHOW GRANTS FOR DB_User@localhost;
`

-110- Save Database into a Dump File

CD %ProgramFiles%\MySQL\MySQL Server 5.1\bin
mysqldump -u YourUser -p YourDB > C:\DUMP.sql
mysqldump -u WebUser -p MediaCenter > X:Backup\Database\DWC.sql

`

-120- Restore Database from a Dump File

(even on a different machine):
Preliminary create all required users and rights – they are not a part of D/B
CD “%ProgramFiles%\MySQL\MySQL Server 5.1\bin”
mysql -u YourUser -p YourDB < C:\DUMP.sql
mysql -u WebUser -p DomainCenter < C:\T\T.sql

`

-130- Rename Database

In MySQL there is no direct command to rename a database.  You have to use these 4 steps instead.

-103.1- Dump old database
“%ProgramFiles%\MySQL\MySQL Server 5.1\bin\mysqldump” -u DB_User -pUserPassword YourOldDB > P:\_SQL\dump.sql

-103.2 Create a new Empty Database and grant all required rights
CREATE DATABASE YourNewDB;
GRANT ALL ON YourNewDB.* TO DB_User@localhost;

-103.3- Restore Database under a new name
“%ProgramFiles%\MySQL\MySQL Server 5.1\bin\mysql” -u DB_User -pUserPassword YourNewDB < P:\_SQL\dump.sql

-103.4- Delete Original Database (after extensive testing)
DROP DATABASE YourOldDB;

`

-140- Backup Database

This procedure allows to backup MySQL database with one click.

“%ProgramFiles%\MySQL\MySQL Server 5.1\bin\mysqldump.exe” -u DB_User -pUserPassword DB_Name > C:\dump.sql

`

-150- Automate Backup and Save Last 10 days

The same as previous, but allows you to change the name of your dump file using last digit of today’s day:

"%ProgramFiles%\MySQL\MySQL Server 5.1\bin\mysqldump.exe" -u DB_User -pUserPassword DB_Name > X:\BACKUP\MySQL%date:~8,1%.sql

You schedule this command to run at the quietest hour of your site, and after several days, you will see multiple backups of your database similar to output shown on the picture.

Note 1
Your PC date can be in a different format.  The script above relies on date format “Tue 11/09/2010“.  You can check your system date format by typing “echo %DATE%” on a command prompt.

Note 2
This command is smart enough to overwrite the previous backup file.  No additional  slashes or switches are required.

`

-0170- Create a table with FULLTEXT index

This is a MySQL command to create a bbPress table bb_posts.  This table requires a FULLTEXT index:

CREATE TABLE bb_posts ( post_id bigint(20) NOT NULL auto_increment, forum_id int(10) NOT NULL default ‘1’, topic_id bigint(20) NOT NULL default ‘1’, poster_id int(10) NOT NULL default ‘0’, post_text text NOT NULL, post_time datetime NOT NULL default ‘0000-00-00 00:00:00’, poster_ip varchar(15) NOT NULL default ”, post_status tinyint(1) NOT NULL default ‘0’, post_position bigint(20) NOT NULL default ‘0’, PRIMARY KEY (post_id), KEY topic_id (topic_id), KEY poster_id (poster_id), KEY post_time (post_time), FULLTEXT KEY post_text (post_text) ) ENGINE = MYISAM;

`

-180 Move Several Tables from One Server to Another

Plan of action:
(01) Create new MySQL database called Transfer;
(02) Create tables using LIKE;
(03) Copy data into new tables in Transfer different database;
(04) Dump Transfer database into a file;
(05) Move dump file to a new server;
(06) Restore database on a new server;
(07) Copy table from new database to a destination.

Here are all seven steps:

(00) List the tables that your need to be transfer:

use YourDB
show tables like "bb%";

You will get list similar to this one

bb_forums
bb_meta
bb_posts
bb_term_relationships
bb_term_taxonomy
bb_terms
bb_topics

(01) Create a new Transfer database to hold your tables:

CREATE DATABASE Transfer;
GRANT ALL ON Transfer.* TO DB_User@localhost;
FLUSH PRIVILEGES;

(02) Create new tables, one after another:

CREATE TABLE Transfer.bb_forums LIKE YourDB.bb_forums;
CREATE TABLE Transfer.bb_meta LIKE YourDB.bb_meta;
CREATE TABLE Transfer.bb_posts LIKE YourDB.bb_posts;
CREATE TABLE Transfer.bb_term_relationships LIKE YourDB.bb_term_relationships;
CREATE TABLE Transfer.bb_term_taxonomy LIKE YourDB.bb_term_taxonomy;
CREATE TABLE Transfer.bb_terms LIKE YourDB.bb_terms;
CREATE TABLE Transfer.bb_topics LIKE YourDB.bb_topics;

Verify that all of your tables have been created:

USE Transfer
SHOW TABLES like "bb%";

(03) Copy data one table at a time:

INSERT INTO Transfer.bb_forums SELECT * FROM YourDB.bb_forums;
INSERT INTO Transfer.bb_meta SELECT * FROM YourDB.bb_meta;
INSERT INTO Transfer.bb_posts SELECT * FROM YourDB.bb_posts;
INSERT INTO Transfer.bb_term_relationships SELECT * FROM YourDB.bb_term_relationships;
INSERT INTO Transfer.bb_term_taxonomy SELECT * FROM YourDB.bb_term_taxonomy;
INSERT INTO Transfer.bb_terms SELECT * FROM YourDB.bb_terms;
INSERT INTO Transfer.bb_topics SELECT * FROM YourDB.bb_topics;

(04)Dump Transfer database into a file

This step is done outside of MySQL shell.

Open a command prompt.  Use this command:

“%ProgramFiles%\MySQL\MySQL Server 5.1\bin\mysqldump.exe” -u DB_User -pUserPassword Transfer > C:\T\TF.sql

(05) Copy dump file to a new server

(06) Restore dump file on a different server

Login to a destination server and open MySQL Shell.  We need to create an empty database first:

CREATE DATABASE Transfer; GRANT ALL ON Transfer.* TO DB_User@localhost;

Outside of MYSQL Shell, from a command prompt restore Transfer database:

“%ProgramFiles%\MySQL\MySQL Server 5.1\bin\mysql.exe” -u DB_User -pUserPassword Transfer < Q:\T\TF.sql

(07) Move your data to a destination database

This example demonstrates a situation, when all the tables already exist on a destination server / database.  If this is not the case,  you can always create the tables using the trick from step (02).

Make sure that all of your tables make it across:

USE Transfer SHOW TABLES like "bb%";

Now you need to decide what data to copy over.  You always can look inside the tables using simple select statement:

SELECT * FROM bb_topics;

To copy all 7 tables you can just type 7 INSERT commands:

INSERT INTO YourDB.bb_forums             SELECT * FROM Transfer.bb_forums;
INSERT INTO YourDB.bb_meta               SELECT * FROM Transfer.bb_meta;
INSERT INTO YourDB.bb_posts              SELECT * FROM Transfer.bb_posts;
INSERT INTO YourDB.bb_term_relationships SELECT * FROM Transfer.bb_term_relationships;
INSERT INTO YourDB.bb_term_taxonomy      SELECT * FROM Transfer.bb_term_taxonomy;
INSERT INTO YourDB.bb_terms              SELECT * FROM Transfer.bb_terms;
INSERT INTO YourDB.bb_topics             SELECT * FROM Transfer.bb_topics;

If you experience a Duplicate entry error message, you can always  delete offending records:

DELETE from bb_forums WHERE forum_id = 1;