Configurar mysql in SuSE

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

# 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

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!

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

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)

cd /usr/bin/
./mysql_install_db

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

mysqlshow

con el resultado :

+——————–+
| Databases |
+——————–+
| information_schema |
| mysql |
| test |
| tmp |
+——————–+

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)

Activar Demonio de Mysql. 

LIMPIAR ACCESOS ILEGALES, ESTABLECER CONTRASEÑA PARA ROOT 

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

AGREGAR USUARIO(S) de MYSQL 

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

CONECTAR A MYSQL

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