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:
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”.
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.
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.
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..
REFERENCEhttp://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.
REFERENCEhttp://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.
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:
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/
