How can i reate a database from ssh

Hello

I was install nginx and php (fpm) and mysql and i want to know now how can i create a new database (utf8) and set the root user to use it (or create a new user and give correct privileges to use it)?

Can you please the commands that i must use from ssh to do that?

On the mysql i have only the root user and password.

Thank you

 

By default you don't actually need a password to login as root from localhost. Just connect via ssh as a root and then type:

mysql -u root
mysql> create database NAMEofDB

Code (markup):

If you want, you can create a new mysql user:

mysql> grant usage on *.* to SOMEUSER@localhost identified by 'somepassword';

Code (markup):

and then grant him an access to your previously created database NAMEofDB:

mysql> grant all privileges on NAMEofDB.* to SOMEUSER@localhost ;

Code (markup):

 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I think i must login using the mysql root password but how i can do it?

Thank you

 

mysql -u root -p

Code (markup):

 

Ok i did it and then run the first command witout any problems

But when i run:

I got:

?

 

"mysql>" at the begining of this and the next line is just a prompt (showing you must be already logged in to run the command), don't actually type it into the command line. If you are logged in as root just type:

grant usage on *.* to SOMEUSER@localhost identified by 'somepassword';

Code (markup):

 

Ok my mistake

Three more questions

1)On this command i must use a space at the end before ; ?

Or i must use it like this? (As all the rest commands do not use this space)

2)On the net i found a tutorial that says after creating the database to use this command to start using it:

Do i have to use it? It will harm if i use it?

3)Database will work without any problems with php or i must do something else to use it by php scripts?

Thank you

 

Either way is good.

With USE command you just switch between different databases on your mysql server. For now you probably won't need to use it.

Yes it will, as long as you have your mysql server running (on most distros it's /etc/init.d/mysqld start).

Yw.

 

Ok i got them all

About the USE command if i have only one database i don't need to run it?

If i have more and use the USE command only the one that i specify will work?

If i want to use them all?

Or is it as a switch enable/disable and can manually enable which db i want enable?

Thank you

 

Everything you want to do with any of your databases, you can do it without entering it. "Use" command just simplifies the syntax when you want to edit some database from the command line (which you probably won't do very often).

Once again, this command doesn't actually "activate" any of your databases, all of them are there on your mysql server and you can access multiple of them at the same time from your php scripts.

For further reference: http://dev.mysql.com/doc/

 

Ok thanks

How can i know that the new database is character set to utf8 and collation to utf8_general_ci ?

 

When you create the database, just add CHARACTER SET utf8 COLLATE utf8_general_ci after the database name. In your case:

create database MariaDB CHARACTER SET utf8 COLLATE utf8_general_ci;

Code (markup):

 

Ok and for the tables inside they will auto create utf8_general_ci collation if i use for example a vbulletin installation?

 

Yup, it's valid for all the future tables you're going to create within this database.

 

Is there any command to check the tables of a database about character set and collation?

Thank you

 

Now you're not having problems but just asking question after another instead of checking out the documentation. We could do this whole night, except I'm about to log out.

use database_name;
show create table table_you_want_to_chceck;

Code (markup):

For the whole database:

show variables like "character_set_database";
show variables like "collation_database";

Code (markup):

 

Ok sorry last question:

Can i use this to check all tables inside a database?

Thank you and no more questions i promise

 

Just use SHOW TABLE STATUS, it will return full list of parameters for every table.

 

Ok got all info thanks

I will try to find now a way to display all tables info in my ssh window as now it shows only the last tables.

No space for all tables to view them all

I am using Putty.

And i am done

 

Try phpmyadmin.