User Tools

Site Tools


database:mysql:mysql

MySQL Administration

:!: For backup policies see mysql_backup

Set the mysql root password

In order to secure the MySQL database server, it's important to set the MySQL root user password (empty by default).

mysql -u root
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');
exit

See MySQL documenation for additional information.

Create a mysql database and user

Create a new database

Login into the mysql console with user root:

mysql -u root -p

Create the database

mysql> CREATE DATABASE test_database;

Create the user with database access

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON test_database.* TO test_user@localhost IDENTIFIED BY 'top_secret';

Log off from the mysql console:

mysql> exit;

Get the MySQL-Server Status [Optional]

Test the status of mysql:

mysqladmin -u root -p status

Alternatively on servers with multiple database systems you have to specify the socket as well:

mysqladmin -u root -p status --socket=/usr/local/mysql5/var/mysql.socket

Login to mysql console and get database info:

[root@eul0000907 bin]# mysql -u root -p --socket=/usr/local/mysql5/var/mysql.socket
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71 to server version: 5.0.27-log
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show databases;

Get MySQL User Information

mysql> select * from mysql.user;

Settings the default character set

Edit the MySQL configuration on your server (/etc/my.cnf), in order to change the defaulr character set.

These two lines will set UTF-8 as default:

[mysqld]
default-character-SET=utf8
 
[client]
default-character-SET=utf8
/srv/wiki.niwos.com/data/pages/database/mysql/mysql.txt · Last modified: 2010/04/19 10:16 (external edit)