Friday, February 26, 2010

MySQL Basics, MySQL Configuration

SkyHi @ Friday, February 26, 2010

The basics for MySQL are important to understand so you can make adjustments.  The first in the series showed you how to set up a LAMP Server, the second in the series showed you how MySQL needs to be configured and this now shows some of the basic settings that you need to know.

If you are interested in a Live Course for Ubuntu 9.10 we provide that option as well and  the Ubuntu Self-Directed Course is now available.  Otherwise, we hope you find this Free mini-course useful which we will roll out over a number of days..

Basic Commands
There are a number of basic commands that you need to have to be able to use MySQL.  You have already seen how to log:
mysql -p -u root

Once you are in you can list the databases that have been created with this command:

my1

Now you need to be able to create a database that you can use.  Here is the command you would use to create a database called “joomla”.

my2

The topic of adding user accounts can be rather complex.  There are a lot of options to fit all kinds of different scenarios.  To keep things simple for now, we’ll just present you with a couple of examples, and then with a table that will briefly explain the options.

Here is the example of a user who  needs to be able to do everything, including creating new databases.  The only thing he won’t be able to do is to create new user accounts, since that’s your job.  Also, he’ll only be connecting from his own computer, whose hostname is “wk5.example.com”.  His password will be “78YH5R”.

Note:  In these examples, you’ll be setting up accounts so that the users can log on from different clients.  For that to work, you’ll also need to open the /etc/mysql/my.cnf file for editing, and comment out the following line:

bind-address            = 127.0.0.1

by placing a # in front of it.

my3

mysql> GRANT ALL ON *.* TO ‘fred’@'wk5.example.com’ IDENTIFIED BY ‘78YH5R’;
Query OK, 0 rows affected (0.00 sec)

Note the semi-colon at the end of the command.

After you’ve created the account, you can verify the privileges that fred has been granted:

mysql> show grants for fred@wk5.example.com;
+—————————————————————————————————————————-+
| Grants for fred@wk5.example.com                                                                                            |
+—————————————————————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘fred’@'wk5.example.com’ IDENTIFIED BY PASSWORD ‘*5B5BF4EC5CCCB48FE859214A4B27BA4CB038EE0B’ |
+—————————————————————————————————————————-+
1 row in set (0.00 sec)

For the second example,  here is the command that you would use to create privileges for fred on just one database called “joomla”.

Here fred  hasn’t been assigned a permanent computer station, so he’ll be floating around on different computers on the 192.168.7  subnet.

my4

mysql> use joomla;
Database changed
mysql> GRANT ALL ON joomla TO ‘fred’@'192.168.7.%’ IDENTIFIED BY ‘78YH5R’;
Query OK, 0 rows affected (0.00 sec)

Again, you can verify that his privileges were set correctly:

mysql> show grants for ‘fred’@'192.168.7.%’;
+—————————————————————————————————————+
| Grants for fred@192.168.7.%                                                                                   |
+—————————————————————————————————————+
| GRANT USAGE ON *.* TO ‘fred’@'192.168.7.%’ IDENTIFIED BY PASSWORD ‘*5B5BF4EC5CCCB48FE859214A4B27BA4CB038EE0B’ |
| GRANT ALL PRIVILEGES ON `joomla`.`joomla` TO ‘fred’@'192.168.7.%’                                             |
+—————————————————————————————————————+
2 rows in set (0.00 sec)

If you wanted to temporarily revoke privileges, but  leave an account in place for when they return you can use this command:

mysql> REVOKE ALL ON joomla FROM ‘fred’@'192.168.7.%’;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for ‘fred’@'192.168.7.%’;
+—————————————————————————————————————+
| Grants for fred@192.168.7.%                                                                                   |
+—————————————————————————————————————+
| GRANT USAGE ON *.* TO ‘fred’@'192.168.7.%’ IDENTIFIED BY PASSWORD ‘*5B5BF4EC5CCCB48FE859214A4B27BA4CB038EE0B’ |
+—————————————————————————————————————+
1 row in set (0.00 sec)

There are a lot of different privileges that are available with MySQL, here is a table of most of them.

Name of Privilege  What the Privilege Allows
FILE Read and write files on the server host
GRANT OPTION Grant account privileges to other accounts
PROCESS View information about threads that execute within the server
RELOAD Reload grant tables, or flush the logs or caches
REPLICATION CLIENT Inquire about master and slave server locations
SHOW DATABASES See all database names
SHUTDOWN Shut down the MySQL server
SUPER Kill threads, and other supervisory operations
ALTER Alter indexes and tables
ALTER ROUTINE Alter or drop stored functions and procedures
CREATE Create databases and tables
CREATE ROUTINE Create stored functions and procedures
CREATE TEMPORARY TABLES Create temporary tables with the TEMPORARY keyword
CREATE VIEW Create views
DELETE Delete rows from tables
DROP Remove databases and tables
EXECUTE Execute stored functions and procedures
INDEX Create or remove indexes
INSERT Insert new rows into database tables
LOCK TABLES Explicitly lock tables
REFERENCES Unused
SELECT Select rows from tables
SHOW VIEW Show view definitions
UPDATE Make changes to table rows
ALL, ALL PRIVILEGES All operations, except GRANT
USAGE This is a special privilege, that has no privileges

After the “ON” keyword in a command, you can specify whether to apply the privilege globally, just to certain databases, or just to certain database tables.

REFERENCE

http://beginlinux.com/blog/2009/11/mysql-basics/


MySQL Configuration


The previous article on the LAMP Server enables you to get started but you will need to do some configuration with MySQL before you go to far.  This article and the next several articles will help you create a database and learn how to monitor it as well as backup and restore.

The MySQL server provides the database that will be connected with PHP and Apache. Installation is easily done with apt-get.

sudo apt-get install mysql-server

During the process you will be asked to create a password for the root user for MySQL. Note this is not the root user for the Linux system and the accounts should have different passwords. Be sure to write down the password. When the installation program completes, you can use the netstat utility to verify that MySQL is running correctly:

sudo netstat -tap | grep mysql

tcp 0 0 localhost:mysql *:* LISTEN 844/mysqld

Here, you can see that MySQL is active, and is listening on port 844.

Note that you don’t have to use the “sudo” command with MySQL server, even though we’re logged on with the user account instead of the root account.

You can add a password to both the server account and the mysql root account at the same time if for some reason your MySQL server did not get a password set.

mysqladmin -h localhost -u root password “the_password_you_want”

Now, if you try to log on to MySQL without the password, you’ll get this:

mysql -u root

ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: NO)

So, to log on now, you’ll need to add a “-p” in the command-line. (The “-p” just means that you’ll be supplying the MySQL password.)

mysql -p -u root

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> quit

Bye

There may also be some anonymous user accounts installed that don’t have passwords. For best security, you’ll want to get rid of them.

mysql -p -u root

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 16 to server version: 5.0.22-Debian_0ubuntu6.06.2-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> delete from user where User = ”;

Query OK, 0 rows affected (0.03 sec)

mysql> delete from db where User = ”;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql>quit

Bye

Also for security’s sake, you’ll want MySQL to run with a non-privileged user account, instead of as root. For that, you can use the user and group named “mysql”. As luck would have it, the Ubuntu Server installation of MySQL has already taken care of this for you. (Of course, you would still log on to MySQL as “root” instead of as “mysql”.)

There is one other thing you might want to do, though, to keep the riff-raff out of your data directory. That is, take away the “group” and “other” permissions.

sudo chmod -R go-rwx /var/lib/mysql


If you are interested in a Live Course for Ubuntu 9.10 we provide that option as well.  Otherwise, we hope you find this Free mini-course useful which we will roll out over a number of days..

REFERENCE
http://beginlinux.com/blog/2009/11/mysql-configuration/



MySQL Database Creation for Virtual Accounts


Create a Database for Virtual Users
There are a lot of uses for MySQL.  Here are the steps to creating a user database that could be used with a FTP server, mail server or web server.

Login and create the database.

mysql -p -u root

CREATE DATABASE virtual;

Whatever you choose to call the database does not matter except that you need to be consistent in all of the settings.

USE virtual;

This command will select the virtual database, or whatever you called it, so that the rest of the commands are executed on the database of choice.

This next series of commands will set up permissions for the user virtual and set a password for that user so that you can authenticate.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON virtual.* TO ‘virtual’@'localhost’ IDENTIFIED BY ‘Ut63trg44′;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON virtual.* TO ‘virtual’@'localhost.localdomain’ IDENTIFIED BY ‘Ut63trg44′;
FLUSH PRIVILEGES;

This next command sets up the accounts table with a username and password, note the command goes all the way to the semi-colon.

CREATE TABLE `accounts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 30 ) NOT NULL ,
`pass` VARCHAR( 50 ) NOT NULL ,
UNIQUE (
`username`
)
) ENGINE = MYISAM ;

INSERT INTO accounts (username, pass) VALUES(‘tom’, PASSWORD(‘tomspassword’));
The next step will involve use of the Pluggable Authentication Module (PAM).

Now check to see if it created the user and password.

SHOW TABLES;
+——————+
| Tables_in_virtual |
+——————+
| accounts         |
+——————+
1 row in set (0.00 sec)

mysql> select * from accounts;
+—-+———-+——————————————-+
| id | username | pass                                      |
+—-+———-+——————————————-+
|  1 | tom      | *1B8F8905CDA6675FA316A5D50D1B7EC4FBE838B9 |
+—-+———-+——————————————-+
1row in set (0.00 sec)

This confirms the user has been created.

REFERENCE
http://beginlinux.com/blog/2009/12/mysql-create-a-database/


Maintaining MySQL Databases

As a MySQL administrator, you’ll probably end up doing some preventive and corrective database maintenance.  You can use mysqlcheck for both.

First, consider the “virtual” database.  It has  one table called ‘accounts”.  You can check the whole database with the following command:
mysqlcheck -p -u root virtual
Enter password:
virtual.accounts                              OK

Now, what if we just want to check one table of the database?

mysqlcheck -p -u root virtual trivia
Enter password:
virtual.accounts                              OK

As you can see, all you have to do to check just one table is to specify the table name after you specify the database name.  Now, what if you have a database with more than two tables, and you want to check more than one, but not all of the tables?  That’s easy.  Just specify all of the tables that you want to check after you specify the database.

mysqlcheck -p -u root mysql db host proc
Enter password:
mysql.db                                           OK
mysql.host                                         OK
mysql.proc                                         OK

You can also check more than one database at a time.  Let’s say that you want to check the “payroll” and the “contact” databases.

mysqlcheck -p -u root –databases payroll contact
Enter password:
payroll.last_name                                   OK
payroll.first_name                                  OK
payroll.SSN                                         OK
payroll.pay_rate                                    OK
contact.last_name                                   OK
contact.first_name                                  OK
contact.phone_number                                OK

This time, by adding the “–databases” switch, all names that you enter on the command-line will be treated as database names.

It’s also a simple matter to check all databases at once, just by using the “–all-databases” switch:

mysqlcheck -p -u root –all-databases
Enter password:
contacts.names                                     OK
contacts.phone_numbers                             OK
contacts.trivia                                    OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.func                                         OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

You can also use mysqlcheck to perform corrective maintenance.  There’s only one catch, though.  MySQL databases can use two different types of tables–either MyISAM tables or InnoDB tables.  While mysqlcheck can perform checks on either type of table, it can only repair MyISAM tables.

mysqlcheck -p -u root –repair virtual
Enter password:
virtual.accounts                              OK

You can do more extensive repairs by adding another switch:

mysqlcheck -p -u root –repair –extended virtual
Enter password:
virtual.accounts                              OK

Or, you can also do a quick repair:

mysqlcheck -p -u root –repair –quick virtual
Enter password:
virtual.accounts                              OK


REFERENCE

http://beginlinux.com/blog/2009/12/maintaining-mysql-databases/


MySQL Backup and Restore

You can use the mysqldump utility to backup your databases.  The simplest way is to create the backup file in another directory.  Of course, you would want to have the backup directory on another harddrive, in case the main database harddrive were to go bad.

my5
mysqldump -p -u root –opt virtual > /backup/virtual-2010-10-24

Enter password:
cd /backup
ls
virtual-2010-10-24

To restore a database from a backup, invoke the “mysql” command, with the backup file as its input:

my6

mysql -p -u root virtual < /backup/virtual-2010-10-24
Enter password:

Note that the only directory path that you have to specify is the one where you’re storing the backups.  The “mysql” and “mysqldump” programs know where the actual databases are stored.

You could also transfer the backup file to another MySQL server with the scp command:

scp virtual-2010-10-24 root@192.168.0.6

Then, on the machine that you just transferred the file to, create an empty database to load the backup file into:

mysql> create database virtual;
Query OK, 1 row affected (0.01 sec)

mysql> quit
Bye

Finally, load the backup file into the empty database:

mysql -p -u root virtual < ~/virtual-2010-10-24
Enter password:


REFERENCE
http://beginlinux.com/blog/2009/12/mysql-backup-and-restore/