Configurar mysql in SuSE

MySQL Reference Manual

• Install apache, PHP and MySQL
• MySQL Portal
• MySQL installation
• MySQL backup

PrerequisitesMake sure you have superuser (root) privileges and user "mysql" already exists in your system. If not, create one:
# groupadd mysql
# useradd -g mysql mysql
This will be the default user under which the MySQL server will be running. 
Running After the installation of MySQL, it is only installed but it is not running. To make it run, the so called daemon for MySQL (mysqld) must be running. mysqld can be started manually by calling

# rcmysql start

with root privileges. You can check whether it is running by using

# rcmysql status

or make it stop by using

# rcmysql stop

Another option is to start the daemon is during the boot.
Go to YaST -> System -> System Services (Runlevel). Wait a few seconds before YaST has examined all the Runlevels. Then select mysqld and press the enable button.
Alternatively, you can enable MySQL startup at boot time by issuing

# insserv rcmysql 

Securing

Warning After the installation, MySQL runs with empty root password! So anybody can do anything with your databases! Be sure to setup the root password before making your machine publicly available!

MySQL contains script that performs some altering in your MySQL settings that it is more secure, but NOT the ultimate configuration, to use when connected to a network or Internet. It does NOT configure your firewall or attack detection!
The script will perform the following settings:

• set a MySQL root password
• remove anonymous users
• disallow MySQL root login remotely
• remove test databases
• reload privileges table

Be sure that MySQL server is running as described above before running this script. Run then the script by:
# mysql_secure_installation Now answer questions, and after that you have a "more" secure MySQL server. Be still aware that the safety of the MySQL server still depends on the security configuration of the network and openSUSE it is running on!

Warning MySQL administrator (root) password is not the same thing as the password for the root account of your system! They are totally independent!

------------------------------------------ 

additional settings

For proper functioning, MySQL needs a "mysql" database. To create this database, simply run:
# /usr/local/mysql/bin/mysql_install_db --user=mysql The script will create /usr/local/mysql/var/ directory containing the necessary databases. This directory serves as a default storage for all databases you will create. Make sure it is writable by "mysql" system user!

start server, check it, connect

Now you are ready to start your MySQL server for the first time.
# /usr/local/mysql/bin/mysqld_safe --user=mysql &
Hit enter again to get your prompt back. The MySQL server should now be running. To check that server is running and works properly enter
# /usr/local/mysql/bin/mysqladmin version You should get some response about the server software version.
Connect to MySQL server:
# /usr/local/mysql/bin/mysql -u root If you get a welcome message and the prompt changes to mysql>, the server works and everything is fine. If this failed for any reason, it may indicate some problems with your installation/configuration.

set the root password

Now, before you do anything else, set root user's password (!). Stay connected to MySQL and enter:
DELETE FROM mysql.user WHERE User = '';
FLUSH PRIVILEGES;
SELECT Host, User FROM mysql.user; Look for the record that has root in the User column and something other than localhost in the Host column. This is the host_name.
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
SET PASSWORD FOR 'root'@'host_name' = PASSWORD('new_password'); Remember, this is the MySQL superuser for all databases. Therefore you should use a strong password and keep it safe. Later, when you will be writing PHP scripts, do NOT use superuser for accessing databases! The "root" user is meant only for administration purposes. After you are finished, exit MySQL:
quit

restart MySQL server

After everything is set up, restart MySQL server:
# /usr/local/mysql/bin/mysqladmin -u root -p shutdown
# /usr/local/mysql/bin/mysqld_safe --user=mysql & Voila, your MySQL server is up and running!

automatic startup

Set up an automatic startup so you don't need to start MySQL server manually after each system reboot. Go back to the directory where you extracted the downloaded mysql tarball file. Enter
# cp support-files/mysql.server /etc/init.d/mysql
# chmod 755 /etc/init.d/mysql
# chkconfig --add mysql
# chkconfig --level 35 mysql on

further reading

Una vez instalado MySQL (OpenSUSE 10)

A continuación ejecutaremos el script de configuración de MySQL, ubicado (normalmente) en /usr/bin/. Para ello abriremos una ventana de terminal y, con el usuario "root" ejecutaremos:

cd /usr/bin/
./mysql_install_db

Si no ejecutamos este script e intentamos iniciar MySQL se producirá un error como este:

Error 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock'

Inicaremos el servicio (daemon) de MySQL, para ello accederemos a la carpeta /etc/init.d:

cd /etc/init.d

y ejecutaremos el siguiente comando:

./mysql start

con el resultado:

Starting service MySQL done

podremos ver el servicio inicado con el comando:

ps -a

con el resultado:

PID TTY TIME CMD
22371 pts/0 00:00:00 su
22374 pts/0 00:00:00 bash
24168 pts/0 00:00:00 mysqld
24201 pts/1 00:00:00 su
24204 pts/1 00:00:00 bash
24368 pts/1 00:00:00 ps

Para probar MySQL podremos ejecutar el comando "mysqlshow" que nos mostrará las bases de datos de MySQL (creadas por el script de configuración):

mysqlshow

con el resultado :

+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| test |
| tmp |
+--------------------+

desde la línea de comandos también podremos crear tablas, ejecutar consultas SQL, etc. Para ello ejecutaremos el comando "mysql":

mysql

con el resultado:

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3 to server version: 5.0.18

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

ejecutando el comando "show databases" nos mostrará las bases de datos actuales de MySQL:

show databases

con el resultado:

> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| tmp |
+--------------------+
4 rows in set (0.02 sec)

para crear una nueva base de datos:

create database prueba

con el resultado:

>;
Query OK, 1 row affected (0.00 sec)

Para conectarnos a la base de datos creada:

connect prueba

con el resultado:

Connection id: 4
Current database: prueba

Para crear una tabla:

create table tabla_prueba (campo1 varchar(10), campo2 date);

con el resultado:

Query OK, 0 rows affected (0.02 sec)

Para mostrar las tablas de la base de datos a la que estamos conectados:

show tables;

con el resultado:

+------------------+
| Tables_in_prueba |
+------------------+
| tabla_prueba |
+------------------+
1 row in set (0.00 sec)

Para insertar registros en la tabla:

insert into tabla_prueba (campo1) values ('prueba 1');

con el resultado:

Query OK, 1 row affected (0.00 sec)

mysql> insert into tabla_prueba (campo1) values ('prueba 2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tabla_prueba (campo1) values ('prueba 3');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tabla_prueba (campo1) values ('prueba 4');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tabla_prueba (campo1) values ('prueba 5');
Query OK, 1 row affected (0.00 sec)

Para mostrar los registro de la tabla:

select * from tabla_prueba;

con el resultado:

+----------+--------+
| campo1 | campo2 |
+----------+--------+
| prueba 1 | NULL |
| prueba 2 | NULL |
| prueba 3 | NULL |
| prueba 4 | NULL |
| prueba 5 | NULL |
+----------+--------+
5 rows in set (0.05 sec)

espaciolinux.com/foros-tema-t20629.html 

PATH environment

# export PATH=$PATH:/usr/local/mysql/

ESTABLECIENDO GRUPOS Y PRIVILEGIOS DE DIRECTORIOS --------------------------------
Los que no usen Slackware, cambiar permisos en /usr/local/ a la carpeta mysql.

# cd /var/run/

# ls -l   //listamos, y debe aparecer asi...
drwxr-x--- 3 mysql mysql 4096 2006-07-23 23:09 mysql

# chgrp -R users mysql
# chown -R mysql mysql
# chmod -R 0755 mysql

Activar Demonio de Mysql. 

Slackware users

# /etc/rc.d/rc.mysql start
Othe Distros
# mysqld_safe &

LIMPIAR ACCESOS ILEGALES, ESTABLECER CONTRASEÑA PARA ROOT 

# mysql

mysql> drop database test;
mysql> use mysql;
mysql> delete from db;
mysql>flush privileges;
mysql> quit

shell# mysql -u root
mysql> use mysql;
mysql> select user, host, password from user;

+-----------+--------------+---------------+
|   user    |     host    | password |
+-----------+--------------+---------------+
|   root    |  localhost |                |
|            |  localhost |                |
+-----------+--------------+---------------+
2 rows in set (0.00 sec)

mysql> delete from user where not ( host="localhost" AND user="root");

mysql> select user, host, password from user;

+-----------+--------------+---------------+
|   user    |     host    | password |
+-----------+--------------+---------------+
|   root    |  localhost |                |
+-----------+--------------+---------------+
1 rows in set (0.00 sec)

mysql> flush privileges;
mysql> quit

Ahora ya tenemos un solo user de la base de datos llamado root (que ún no tiene prefijada una contraseña), que es el único que nos permite acceder al prompt de mysql para gestionarla

# mysql
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
# mysql -u root
mysql> set password for root@localhost = password('mypassword');
mysql> use mysql

mysql> select user, host, password from user;
+-----------+--------------+------------------------------------------+
|   user    |     host    |       password    |
+-----------+--------------+------------------------------------------+
|   root    |  localhost |   *AD5156SA1DA5S61D       |
+-----------+--------------+------------------------------------------+
1 rows in set (0.00 sec)mysql> flush privileges;
mysql> quit

# mysql -u root
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
shell# mysql -u root -p
mypassword:
mysql> use mysql
mysql> flush privileges
mysql> quit

AGREGAR USUARIO(S) de MYSQL 

# mysql -u root -p

mysql> use mysql;
mysql> grant select, insert, alter, update, delete, drop, create on *.* to usuario@localhost identified 'password';
mysql> select user, host, password from user;
+-----------+--------------+------------------------------------------+
|   user    |     host    |       password    |
+-----------+--------------+------------------------------------------+
|   root    |  localhost |   *AD5156SA1DA5S61D       |
| usuario |  localhost |   *F51F5F51FDS516DF       |
+-----------+--------------+------------------------------------------+mysql> flush privileges;
mysql> quit

Agregamos un usuario llamado: "msu", el cual le dimos los sgtes. privilegios

insert, select, update, delete, drop, create
TABLA DE PRIVILEGIOS 

Host                        %             %             %

User                     phpuser  phpuser  phpuser
Select_priv               Y             Y              Y
Insert_priv                 Y             Y              Y
Update_priv              Y             Y              Y
Delete_priv               Y              Y              Y
Create_priv               Y              Y              Y
Drop_priv                  Y               Y              Y
Grant_priv                  N             N              N
References_priv       Y               Y              Y
Index_priv                  Y               Y              Y
Alter_priv                   Y               Y              Y

CONECTAR A MYSQL

1. # mysql -u usuario -pmypassword:
   mysql> quit
2. shell# mysql -p
   mypassword:
   mysql> quit
   

Para acceso remoto a la base de datos se debe comentar en el archivo /etc/mysql/my.cnf la sgte. línea:

bind-address     = 127.0.0.1