Creating SQL database

[agent007]

hi all,

I've installed apache+mySQL.....how do I create a new database from the command line? I also need to add a user and password....can this be done from the command line?

thanks.

[Tux]

==> Create User agent007:

# mysql -u root
> use mysql;
> insert into user (host, user, password) VALUES ('localhost', 'agent007', PASSWORD('yourpass'));
> flush privileges;
> exit;

==> Create database agentdb with modification rights given to user agent007:

# mysql -u root
> use mysql;
> create database agentdb
> grant all privileges on agentdb.* to 'agent007';
> flush privileges;
> exit;

[Void Main]

You might also look in to some of the other MySQL commands for alternate ways of doing that. Commands like mysqladmin and mysqlaccess.

[agent007]

hi guys!

Thanks for the help......After issuing the command
mysql -u root

The following error comes up:

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

What does it mean?

thanks..

[Void Main]

Did you set a password for your root MySQL user? If so you need to add the "-p" parameter so it will prompt you for your password.

[agent007]

oh great...I dont think so...I was playing aound earlier before and may have put a password which i have no idea of now...Is there a way to reset it? i'll try uninstalling and reinstall mysql to c if it gets rid of it.....

thanks..

[agent007]

Right, reinstalling did not work..SO now what? Is there a way to reset the password?

[Tux]

Try using just 'mysql', if that works ill tell you how to set a root password.

[Void Main]

No, "reinstalling" won't work unless you actually delete the MySQL database between the "remove" and the "install". The MySQL system database is created at install time. When you remove MySQL it only removes the program files and not the database that was created, which is normally a good thing. In MySQL packaged for RH/FC the MySQL databases are physically stored under the /var/lib/mysql directory.

Having said that, yes, there are other ways to recover a lost MySQL root password:

http://www.mysql.com/doc/en/Resetting_permissions.html

Please also read the reader comments at the bottom of the above page before doing anything.

[Tux]

No, "reinstalling" won't work unless you actually delete the MySQL database between the "remove" and the "install". The MySQL system database is created at install time. When you remove MySQL it only removes the program files and not the database that was created, which is normally a good thing. In MySQL packaged for RH/FC the MySQL databases are physically stored under the /var/lib/mysql directory.

Having said that, yes, there are other ways to recover a lost MySQL root password:

http://www.mysql.com/doc/en/Resetting_permissions.html

Indeed, although as far as I understand he doesn't currently have a root password?