Bienvenido! - Willkommen! - Welcome!

Bitácora Técnica de Tux&Cía., Santa Cruz de la Sierra, BO
Bitácora Central: Tux&Cía.
Bitácora de Información Avanzada: Tux&Cía.-Información
May the source be with you!

Thursday, October 7, 2010

My MySQL installation and configuration

Create a symbolic link called "mysql" pointing to the MySQL installation directory, in order to make referring to it from elsewhere easier:
# ln -s /usr/local/mysql-5.0.37/ /usr/local/mysql This way we can always refer to MySQL installation directory as /usr/local/mysql . The obvious advantage is that if you install PHP with the --with-mysql=/usr/local/mysql option (see PHP 5 Installation Guide), it won't stop working if the name of the MySQL installation directory changes in the future (if you upgrade your MySQL for instance). 

# /usr/bin/mysql_install_db
Installing MySQL system tables...
101007 11:14:57 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
Filling help tables...
101007 11:14:57 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h password 'new-password'
Alternatively you can run:
which will also give you the option of removing the test databases and anonymous user created by default.  

This is strongly recommended for production servers.
You can start the MySQL daemon with:
rcmysql startYou can test the MySQL daemon with mysql-test package
Please report any problems with the /usr/bin/mysqlbug script!

# joe /etc/my.cnf
# groupadd mysql
# useradd -g mysql mysql
# rcmysql status
# mysqlshow# /usr/bin/mysql_secure_installation
In order to log into MySQL to secure it, we'll need the current password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank, so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation.
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] n
 ... skipping.
By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them.  This is intended only for testing, and to make the installation go a bit smoother.  You should remove them before moving into a production environment.
Remove anonymous users? [Y/n] Y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'.  This ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y
 ... Success!
By default, MySQL comes with a database named 'test' that anyone can access.  This is also intended only for testing, and should be removed before moving into a production environment.
Remove test database and access to it? [Y/n] Y
 - Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
 ... Failed!  Not critical, keep moving...
 - Removing privileges on test database...
 ... Success!
Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
Reload privilege tables now? [Y/n] Y
 ... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MySQL installation should now be secure.
Thanks for using MySQL

start server, check it, connect

# /usr/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/bin/mysqladmin -u root -p version
Enter password:
/usr/bin/mysqladmin  Ver 8.42 Distrib 5.1.46, for suse-linux-gnu on x86_64
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.1.46-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysql/mysql.sock
Uptime:                 1 hour 13 min 29 sec

Connect to MySQL server:
# /usr/bin/mysqladmin -u root (without password set)
# /usr/bin/mysqladmin -u root -p
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 = '';
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:
restart MySQL server:
# /usr/bin/mysqladmin -u root -p shutdown 
# /usr/bin/mysqld_safe &
[1] 5588
arena:/etc # 101007 12:40:54 mysqld_safe Logging to '/var/log/mysql/mysqld.log'.
101007 12:40:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

# cd /var/lib/mysql

Connecting to the sql server:
# /usr/bin/mysql -u root -p

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
Creating a database
arena:/home/edith/Desktop # mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.46-log SUSE MySQL RPM

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database gestrack;
Query OK, 1 row affected (0.00 sec)
mysql> GRANT ALL ON gestrack.* TO edith@localhost IDENTIFIED BY "edith777";
Query OK, 0 rows affected (0.00 sec)
mysql> quit
arena:/home/edith/Desktop # exit

As standard user edith
edith@arena:~/Desktop> mysql -u edith -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.46-log SUSE MySQL RPM
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
| Database           |
| information_schema |
| gestrack           |
2 rows in set (0.00 sec)

No comments: