MySql Tutorial – Create and Backup the Database
MySQL Tutorial for Beginner
MySQL is one of the popular database management system. Developers can make use of SQL query language to access and manipulate data. One can use MySql databases management system from different kinds of applications and websites. Creating mysql database in php is one of the essential tasks that need to be performed by the php developers. Similarly, backup is another important task that should be performed to avoid data loss. There are a number of ways to create and backup MySql databases. You can even opt for remote dba support for MySql Monitoring and production.
In this SQL basics tutorial, different steps that must be followed here – you will learn to create and backup the mysql database. We will discuss following steps like –
- Creating a new database
- Adding tables to records
- Creating a backup of the database
- Restore the database
MySQL is an intimidating program where the commands need to be entered through the command prompt. No additional visual interface is provided to enter the commands. Due to this, one should have a basic idea of creating and manipulating the database to save time. Below is a step by step tutorial on how to create and backup the database.
Read more:MySQL Tutorial
MySQL commands to create database
MySQL is an open source database management system and one should make use of SQL commands to create the database. In this tutorial, we will teach you how to create mysql database backup.
Before you start with coding, you need to create a user account and login as root user. Enter the command CREATE DATABASE . Make sure not to include spaces in the database name.
MySQL create database and user example – tcs_employees is the database name which we are going to use throughout the tutorial.
Display the list of databases that are available by entering the command SHOW DATABASE. This command will list all the databases that were stored by you. You will find mysql and a test database, which can be ignored.
Select the database and start editing by entering the command USE tcs_employees. As soon as you press enter, you will see the message ‘database’ changed and the active database has now been tcs_employees.
The next step is to create a table in mysql database that includes the database information. For this, one should enter all the table formatting commands initially. Enter the command:-
CREATE TABLE employees (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name CHAR (25), designation CHAR (25))
This will create a table ‘employees’ with three fields- ID, name and designation. The INT command creates an ID field that contains only numbers. The NOT NULL command makes sure that the field is not left empty.
Create the entry in table by entering the command:-
INSERT INTO employees (id, id, name, designation) VALUES ( ‘James’, ‘project manager’)
The ID field contains the NOT NULL command, and hence by entering NULL in the INSERT command, it will be automatically incremented to 1.
Create more entries by using a single command. To enter more employees, use the below command
INSERT INTO employees (id, name, designation) values (‘Jenny’, ‘Team leader’), ( ‘Jack’, ‘Database administrator’)……. And so on.
Once the step 6 is completed, we can say that the basic database has been created. To return specific results, one can enter queries such as – SELECT * FROM tcs_employees, which will return the entire database. To run an advanced query such as – sorting the employees in alphabetical order rather than employee id, you can enter SELECT employee, name FROM tcs_ employees order by name. This will sort the list of employees in alphabetical order.
Techmagnate offers Web Application Development Services
MySQL database is usually utilized to store important data. Hence, one should never forget to backup the data. The next thing we are going to learn in this tutorial is to backup and restore the database. There are two easy methods to backup and restore the database.
1.Backup from command line and restore the MySQL database
If you have Telnet access to the web server, you can make use of mysqldump command that connects the server and creates a dump file. The dump file includes a number of SQL statements that are essential to re-create the database. You can even specify certain tables of the database that you want to backup with mysqldump command.
Sometimes, you may need to create backup database more than one database at a time. In that case, you can use the mysqldump command followed by the list of databases you would like to backup. The database names should be separated by space.
The next step is to restore the database. This can be done by creating an appropriately named database on the target machine and load the file to that machine by using mysql command
Mysql –u [uname] –p [pass] [db_ < [backup file]
If you want to restore an existing database, you should use the mysql import command.
2. Backup and restore the database using PhpMyAdmin
Most of the service providers use PHP. If you have PhpMyAdmin installed, you can backup and restore the database by using the below commands.
Open PhpMyAdmin and select the database from the list provided on the left of the screen.
Click to select all to choose all the tables. Click all the options in the SQL options and save. Click on ‘Go’, which opens a dialog box prompting you to save the file in the local drive.
Once this is done, the backup process gets completed.
The next step is to restore the database, which can be done by creating a database and selecting it. Click on the SQL link, where you will get a screen to enter the SQL commands.
One can even upload the SQL file.
Use the browse button to search the database file. Once you press the ‘Go’ button, the backup will be uploaded and the database is re-created.
There are a number of other methods to perform backups in MySQL. Each method has its own advantage and drawbacks. Some are easier to implement and some are highly technical. The backup and restore scheme depends completely on your individual needs.
- How to Install MySQL Database? – Go here
- For more Advanaced SQL Database method – Go here
- Download MySQL workbench – Go here
- Download MySQL Database for Windows – Go here
- MySQL Free tools to manage your Database – Go here
- Learn MySQL from W3c – Go here
- Free MySQL Database hosting – Go here
More Tutorials on Techmagnate
Author Bio – Kevin is an expert on database software and provides remote dba support. He has the passion for writing technical articles and has written many blogs, articles, press release and web content for many websites