Title: Manage Mysql
Course: CIS 208: Linux/UNIX III: Intermediate System Administration, Networking, and Security ~ For IGNITE Review > Section: Learning Modules

Manage Mysql

Mysql is the most commonly used open source database, and most web based applications are designed to work with mysql.  We'll take a look at installing mysql, securing mysql, and using  mysql.

- To install the server and the client applications in Red Hat like distributions, run the following commands.

yum install mysql-server mysql

- The first step in securing mysql is to create a password for the root account.  This should be done immediately after installing mysql-server.  Replace "newpassword" with the actual password and "fqdn.domain.tld" with the server's hostname.

service mysqld start
chkconfigg mysqld on
mysqladmin -u root -h fqdn.domain.tld password 'newpassword'
mysqladmin -u root password 'newpassword'

- Whenever a new database needs to be created, create a mysql user account specifically for this database.  For example, if blogging software requires a database named webblog, create a user named webblog which will match the name of the database.  While matching names are not a requirement, it does make management of databases and user permissions much easier.  The following commands create a database and a user, then assigns permissions to the user.

mysql -u root -p
create database webblog;
create user 'webblog'@'localhost' identified by 'topsecret';
grant all on webblog.* to 'webblog'@'localhost';
flush privileges;
exit;

- It is good to plan regular backups of the database.  Create a directory such as /var/lib/mysql-bu to store the archive file.  The following command will create an archive of the database with no down time.

mysqldump -opt -u root -p webblog > /var/lib/mysql-bu/webblog.sql

- If you later need to restore the database from an archive file, run the following command.

mysql -u root -p webblog < /var/lib/mysql-bu/webblog.sql

We have already seen the client program, mysql, in the examples above.  Note that mysql try to authenticate with the OS user account and no password by default.  Add the -u switch to user a different user account and the -p switch to be prompted for a password.  Optionally the name of a database can be added to start the client using a specific database.

After a successful login, note that the prompt changes to "mysql>" and that typing the [Enter] key does not submit a command for processing.  It is necessary to type a semicolon (;) or an escaped g (\g) to signify the end of command prior to typing [Enter].  Type "exit;" to end the session.