How to create a new database in MySQL
The MySQL database is a widely used relational database, installation of which comes with different web server bundles like WAMP (Windows) and LAMP (Linux). It can ofcourse also be installed separately. In this Wiki article we will go through the steps on how to create a new database in MySQL from scratch and how to do some adjustments on it plus check the database validity.
Mobile devices can access databases on web servers as wired computers can, reading and writing data from them e.g. to populate fields or even tables in the application or to send data for remote server database for analysis and storage.
After reading this article, you should be able to create a MySQL database, which you can then operate and enhance by adding tables and data e.g. from the user of your mobile application.
The phpMyAdmin console is an alternative way to perform these tasks, but it is not in the scope of this article.
This Wiki article assumes, but does not require an installed WAMP environment, which you could prepare by following the instructions on these articles:
Furthermore, this article is written from the perspective that the MySQL will reside on a local server (localhost) and that the commands are not executed over to a remote host.
Follow these steps to create a new database:
Start the MySQL server
If the MySQL server has not yet been started, please do so. In e.g. WAMP this can be done by opening it’s menu (left-click), selecting MySQL, Service, Start/Resume.
Note: if the server is not running, you will not be able to login.
Create the new database
To create a database, we will use the mysqladmin program included with the MySQL installation. With this program we can administrate the MySQL database server and perform tasks like creating and deleting databases, shutdown the server and to update privilege tables plus to view running MySQL processes.
For this article we will create database called e.g. “users”, into which one could store and maintain information about web service or a mobile application users (should you already have created same database earlier, please select another name to avoid conflict).
Open a command window (in Windows Start menu/Run/cmd) and navigate to the directory below (default directory for a fresh WAMP installation).
Execute the “mysqladmin” program:
C:\wamp\bin\mysql\mysql5.1.36\bin> mysqladmin -u root -p create users
The database will be created and the mysqladmin program returns back to the command prompt, no messages are shown if there were no errors or the database did not already exist.
If you try to create a database that already exists, you will get the following message. In this case if you will execute the same command again as done previously:
mysqladmin: CREATE DATABASE failed; error: 'Can't create database 'users'; database exists'
Take the new database in use
After the database exists, it can be taken in use. To try this out, do as follows:
Start The MySQL console. It can be opened from the WAMP menu or alternatively enter “mysql” in the command prompt of the current directory. The example flow below assumes the password was set earlier (if not, just press Enter to bypass).
C:\wamp\bin\mysql\mysql5.1.36\bin>mysql -u root -p
Enter password: ***
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.36-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
When in monitor program, you can issue a “use” command accompanied with the database name:
mysql> use users;
MySQL will inform it did change the database:
You can further as MySQL to show the tables in the database, which will result to empty as there currently are none:
mysql> show tables;
Empty set (0.00 sec)
After a successful database creation, you could secure the database by setting it’s privileges. For database security it will be essential that the table is assigned to a user with reduced rights, especially if it is being accessed remotely. This would make sure the user who requests data from the database or wants to enter new data, has no chance to issue further critical administrative commands that could put the whole database server under a risk.
A separate Wiki article handles exactly this: creating a new user and setting the required privileges for the database created here.
--13:21, 27 May 2010 (UTC)