Hello all,
I have just installed suse 11.2 and want to set up mysql. I can start mysql from the terminal window by typing ‘mysql’ but thats it.
I believe I have to create a users table and then the user root or at leats set roots password. Is there a ‘how to’ for first time mysql set up?
Start the mysql service from YaST. The mysql command is just the client.
Run the script
mysql_secure_installation
This will prompt you for a root password (root in mysql, not root in Linux, so can be different passwords).
The database user and database name are application dependent. Usually the app will either ask you to provide the root password so that it can create the database and assign permission to the database user, or ask you to do something like this from the CLI mysql client.
create database joomla;
grant all on joomla.* to username@localhost identified by 'somepassword';
I could explain all here, but there’s very good info in the docs that come with a mysql install. Step by step instructions can also be found googling around a bit.
FWIW: amarok contains a nice set of instructions on how to setup a database and a user for it.
MySQL has to be started at boot, done so through Yast-System-Runlevel, activate mysqld.
Good luck, get back here if you don’t succeed.
When running the script I get:
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
I tried hitting enter for none.
I recall using a shell command that set roots password and if I try using it I get the same thing
You must have set it before then. Too bad if you forgot it. Or perhaps you already had a database in /var/lib/mysql from a previous install of the package. For reasons of not destroying data, reinstalling the mysql packages doesn’t remove /var/lib/mysql where the databases (and the password) are kept.
I find it hard to believe that there is no fix other than re-installing suse 11.2 and starting over
Here’s what you do if you have no data in the mysql that you want to keep and you have forgotten the root password.
Uninstall the mysql package (server).
Remove /var/lib/mysql with rm -fr.
Reinstall the mysql package. This will recreate /var/lib/mysql.
Run mysql_secure_installation and set the password.
There is another way involving disabling authentication in /etc/my.cnf that you can read about in the online mysql documentation at dev.mysql.com. You would use this method if you have data that you want to preserve.
There was a shell command I used to set the mysql password (not run from within mysql) and when I used it i didn’t specify the host name with the user name. I feel that is where I went wrong, is there a fix for that? What is the shell command to set the mysql password in linux?
As soon as you have started mysqld in YaST, open a terminal window and enter
mysql -u root
The prompt will change to
mysql>
The default installation has two hosts: localhost and the HOST_NAME of your installation. You can check this by entering
SELECT Host, User FROM mysql.user;
Then enter
SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(`password’);
SET PASSWORD FOR ‘root’@‘HOST_NAME’ = PASSWORD(‘password’);
replacing ‘HOST_NAME’ with the hostname of your installation.
Unless you intend to have anonymous users access your databases, enter
DELETE FROM mysql.user WHERE Host = ‘localhost’ AND User = ’ ';
FLUSH PRIVILEGES;
If you do not flush privileges, the changes you have just made will not become active until you next boot Linux.
PS The CAPS are just to identify significant terms; you don’t need to use them.
Sorry the next to last command should be
DELETE FROM mysql.user WHERE User = ’ ';
to make sure you delete both anonymous users.
After that follow the earlier contributions about adding users.
Thanks to all for help, I blew suse 11.2 away and started over since it was a fresh install anyway. Ran the script previously mentioned and now have working mySql server.
Wow, thanks! 