Category Archives: Mysql

Extracting a Database From a mysqldump File

If you make full databases backup in one single file, you could need sometime to restore only one db from that dump file.

Here you are a easy way to extract it:

grep -n "Current Database: " mysqldumpfile.sql

19:– Current Database: `database1`
3546:– Current Database: `dabatase2`
6127:– Current Database: `database3`
6491:– Current Database: `database4`
10001:– Current Database: `database5`
10371:– Current Database: `database6`
11626:– Current Database: `database7`
11634:– Current Database: `database8`
16577:– Current Database: `database9`
18591:– Current Database: `database10`
18687:– Current Database: `mysql`
19336:– Current Database: `database11`
19589:– Current Database: `database12`
20278:– Current Database: `database13`
22557:– Current Database: `database14`
22961:– Current Database: `database15`
23320:– Current Database: `database16`
23719:– Current Database: `database17`

The previous output show you the database name and in which line it start. You only have to take the lines between that and the next database start minus one. If we want to extract database9:

sed -n '16577,18590p' mysqldumpfile.sql > database9.sql

With that you will get a dump only with database9 data.

Could be you don’t want to extract one database from the dump file, but you want all databases but one. The process it is the same, you have to know where each database start, and run the sed command but this time, it will be everything but the indicated. Let say we want to extract everything from the mysqldump file but mysql database:

sed -n '18687,19335!p' mysqldumpfile.sql > mysqldumpfilewithoutmysqldb.sql

How to copy tables in MySQL

My first instinct was to make an export/import, but there is smarter ways to make it:

First you have to create the table with the same schema:

CREATE TABLE database2.newtable LIKE database1.table;

Then you can copy data from table to newtable:

INSERT INTO database2.newtable SELECT * FROM database1.table;

If you need, you can add WHERE clause to the select, also, if you are working in the same db you don’t need to specific the database name.

How to create user

This command will create a user in mysql with full access to the selected database:
mysql> GRANT all ON DATABASE.* TO USER@localhost IDENTIFIED BY 'PASS';
mysql> FLUSH PRIVILEGES;

DATABASE: The database where the user will have full access.

USER: The username that will have full access to the database. It will be created.

PASS: The password for this user.

How to create a database

Here you are a note to create a database in mysql:

mysql> create database DATABASE_NAME;

DATABASE_NAME: Change this for the database name you want to create.