How to create a MySQL database on my VPS?

[Jovani]

Hi everyone,

Are there any ways to create a MySQL database on my VPS without using phpMyadmin or user interface?
I mean that I want to create database, username, password for db by commands on my console. Is it possible?

Give me any tips?

 

[hoangvu]

Create database and database user name
If you are going to install WordPress blog on your VPS, it is important to have a database to store your information. Now that we have installed MySQL on the VPS then, will now proceed to log into MySQL with the following command and enter the password in MySQL.

#mysql u root-p

At this point you should have been in the MySQL command, so you must use SQL language, on the screen, you will see your command are now starting the mysql> # as usual instead of your_server>_

View attachment 788

For example, now you will create a database with the following information:
• Database Name: myblog
• Database User: myuser
• Database Password: mypass

And first, you will need to build a database name, and type.

CREATE DATABASE myblog;

and create a user database

CREATE USER@localhost myuser;

Create a password for your new database

SET PASSWORD = PASSWORD FOR myuser@localhost ("mypass");

Assign database and database user name to work together, otherwise known as the database user granted access to the database you just created by follow this code.

GRANT ALL PRIVILEGES ON myblog.* Identified BY TO myuser@localhost 'mypass';

Refresh MySQL

FLUSH PRIVILEGES;

And exit MySQL

exit

So, okay, now we've got a database with follow information and you can install your wordpress blog according to information

• Database Name: myblog
• Database User: myuser
• Database Password: mypass

Good luck!

 

[TeoC NETCreator]

Hello!

--- HOW TO CONNECT TO SERVER

You can do it from your console:
If your computer OS is Linux, you can connect to your server from Linux Terminal. In Windows, you need a software like Putty.

For linux users:

Open terminal and connect to your server:

ssh [email protected]

You should login as root for the best experience with. So, ssh [email protected]

For windows users:

Login through Putty, by entering server (username and IP) details in Putty default window.

--- HOW TO INSTALL MYSQL

Now, if you don't have MySQL installed on your server, you can do it by running:

sudo yum install mysql-server

and

sudo /sbin/service mysqld start

--- HOW CREATE DATABASE AND USER

1. After installs completed, log into your MySQL database as root by typing:

mysql -p

2. Create a database:

CREATE DATABASE new_database_name_here;

3. Create an user and set password:

GRANT ALL PRIVILEGES ON new_database_name_here.* TO 'new_username_here'@'localhost' IDENTIFIED BY "new_database_password_here";

4. Flush privileges

flush privileges;

5. Exit MySQL:

exit;

If you have PHP installed on, you can connect to your db through PHP and then run same jobs.

 

[hoangvu]

Hey Teo
After install Mysql

sudo yum install mysql-server

how to know root username and change its default password?

 

[TeoC NETCreator]

Hello!

-- ROOT USERNAME

The root username is always "root" (without quotes).

-- CHANGE DEFAULT ROOT PASSWORD (from console)

Connect through Terminal or Putty exactly as I told above.

If you are using Ubuntu or Debian:

sudo /etc/init.d/mysql stop

For CentOS, Fedora, and RHEL:

sudo /etc/init.d/mysqld stop

Start MySQL in safe mode

sudo mysqld_safe --skip-grant-tables &

Login (without password)

mysql -u root

Select the database

use mysql;

update user set password=PASSWORD("YOUR NEW PASSWORD HERE") where User='root';

flush privileges;

Exit

quit

or

exit

Restart MySQL
Ubuntu and Debian

sudo /etc/init.d/mysql stop

and

sudo /etc/init.d/mysql start

CentOS, Fedora and RHEL

sudo /etc/init.d/mysqld stop

and

sudo /etc/init.d/mysql start

Now, you can try to login with the new password:

mysql -u root -p

(type the password when prompted)

Hope this helps!

 

[Maxwell]

@ TeoC NETCreator, an additional question:
Using mysql root password, can we login to PhpMyadmin for all mysql databases created before?

 

[Brainscott]

Yes this is one of the most easiest method to create a username and password for a database and then use it whenever needed. This code was very helpful.