Installation and Configuration of MySQL

reference: http://en.opensuse.org/Mysql#Installing_MySQL

MySQL server from installation repository

Install the MySQL server by selecting it in YaST. From this point the MySQL server is only installed, but not configured or running. The next step is to make the MySQL server run and start at boot time.

Go to YaST -> System -> System Services (Runlevel). Wait a few seconds before YaST has examined all the Runlevels. Then select mysql and press the Enable button. Note: At this point, the root MySQL user password has not been set. If MySQL administrator (root) password is not set, anyone can access databases as root, read and change data, delete them, set root and user passwords and lock you out! Set the MySQL root password as follows:

mysqladmin -u root password "yournewpassword"

From this point OpenSUSE has a properly configured MySQL server. [edit]

Here are some simple tests to verify that the server is up and running and responding to commands:

./mysqladmin version

and

./mysqladmin variables

---

After MySQL is installed and ready to use, here are some commonly used commands. After a user is logged in, commands within MySQL end with a semicolon. Additional commands can be found here: http://www.pantz.org/software/mysql/mysqlcommands.html

Create a database on the sql server.

create database [databasename];

List all databases on the sql server.

show databases;

Switch to a database.

use [db name];

List all the tables in the db.

show tables;

List a database's field formats.

describe [table name];

Change a users password (from shell).

[mysql dir]/bin/mysqladmin -u root -h hostname.example.org -p password 'new-password'

Change a users password.(from MySQL prompt).

SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

Allow the user "bob" to connect to the server from localhost using the password "passwd"

grant usage on *.* to bob@localhost identified by 'passwd';

Switch to mysql db. Give user privilages for a db.

grant all privileges on databasename.* to username@localhost;