MySQL on Ubuntu 8.04 +++++++++++++++++++++++ :Posted: 2008-06-07 01:01 :Tags: Web I do this sort of MySQL administration a lot so I thought I'd document it this time. Much of the information is taken straight from the excellent `MySQL 5.0 manual `_. Installation ======================= Get the latest MySQL:: sudo apt-get install mysql-server Set a root password:: Package configuration ┌────────────────────┤ Configuring mysql-server-5.0 ├─────────────────────┐ │ While not mandatory, it is highly recommended that you set a password │ │ for the MySQL administrative "root" user. │ │ │ │ If that field is left blank, the password will not be changed. │ │ │ │ New password for the MySQL "root" user: │ │ │ │ _______________________________________________________________________ │ │ │ │ │ │ │ └─────────────────────────────────────────────────────────────────────────┘ You have to repeat it:: ┌──────┤ Configuring mysql-server-5.0 ├───────┐ │ │ │ │ │ Repeat password for the MySQL "root" user: │ │ │ │ ___________________________________________ │ │ │ │ │ │ │ └─────────────────────────────────────────────┘ Once the install is finished MySQL will be running. If you ever want to update the root password you can do it like this (you don't need to be the UNIX ``root`` user):: mysqladmin -u root -p password 'new-password' Enter the password you just entered during the installation when you see the ``Enter password:`` prompt. Listening on All Interfaces =================================== Remote access is disabled on Ubuntu by default. If you want MySQL to listen on all interfaces, not just localhost, edit ``/etc/mysql/my.cnf`` and comment out the line ``bind-address = 127.0.0.1:`` :: sudo vim /etc/mysql/my.cnf Then we restart MySQL:: sudo /etc/init.d/mysql restart Now check that networking is enabled. Run:: netstat -tap | grep mysql You should see a line like this one:: tcp 0 0 *:mysql *:* LISTEN 22565/mysqld It looks like this if you only listen on localhost:: tcp 0 0 localhost:mysql *:* LISTEN 9119/mysqld The MySQL Command Prompt ==================================================== You can now login to the mysql server with this:: mysql -u root -p You'll need to type in the password that you entered earlier:: root@james-laptop:~# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.0.51a-3ubuntu5.1 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> You can exit by typing ``exit``. Existing Databases ======================================= MySQL sets up two databases by default:: mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) mysql> use information_schema ; 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> SHOW TABLES; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | KEY_COLUMN_USAGE | | PROFILING | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | STATISTICS | | TABLES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | +---------------------------------------+ 17 rows in set (0.00 sec) 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> SHOW TABLES; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | proc | | procs_priv | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 17 rows in set (0.00 sec) You shouldn't modify these databases directly unless you are a MySQL expert and know precisely what you are doing. For testing various commands you are much better creating your own database. Creating a Database ================================================= Create a database:: mysqladmin -h localhost -u root -p create first This creates the directory ``/var/lib/mysql/first``. You can also create a database from the ``mysql>`` command prompt with the ``CREATE DATABASE first DEFAULT CHARACTER SET UTF8;`` SQL which also allows you to set the default charcter set. You might think you could achieve the same by using ``--default-character-set=utf8`` with the ``mysqladmin`` command above but it doesn't work. You can get information about a database with this SQL:: mysql> SHOW CREATE DATABASE first; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | first | CREATE DATABASE `first` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec) Creating a User =========================== * http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html * http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html * http://dev.mysql.com/doc/refman/5.0/en/adding-users.html As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the username you specify. When you issue requests after connecting, the system grants privileges according to your identity and what you want to do. You can create MySQL accounts in two ways: * By using statements intended for creating accounts, such as ``CREATE USER`` or ``GRANT`` * By manipulating the MySQL grant tables directly with statements such as ``INSERT``, ``UPDATE``, or ``DELETE`` The preferred method is to use account-creation statements because they are more concise and less error-prone. Operating system usernames are completely unrelated to MySQL usernames which can be a maximum of 16 characters long. Many MySQL clients use the current UNIX username as a default if a username is not specified as a convenience but the two are not related. Likewise MySQL and UNIX account passwords are completely unrelated. MySQL encrypts passwords using its own algorithm. This encryption is different from that used during the Unix login process. MySQL password encryption is the same as that implemented by the ``PASSWORD()`` SQL function which you use if you are inserting new users directly into tables - you shouldn't usually do that though and the GRANT statements mentioned below handle the encryption for you anwyay. Incidentally, UNIX password encryption is the same as that implemented by the ``ENCRYPT()`` SQL function. First, use the ``mysql`` program to connect to the server as the MySQL ``root`` user (not the same as the root system user):: mysql -u root -p mysql After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:: mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost'; mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost'; The accounts created by these GRANT statements have the following properties: * Two of the accounts have a username of ``monty`` and a password of ``some_pass``. Both accounts are superuser accounts with full privileges to do anything. One account (``'monty'@'localhost'``) can be used only when connecting from the local host. The other (``'monty'@'%'``) can be used to connect from any other host. Note that it is necessary to have both accounts for ``monty`` to be able to connect from anywhere as ``monty``. Without the localhost account, the anonymous-user account for localhost that is created by ``mysql_install_db`` would take precedence when ``monty`` connects from the local host. As a result, ``monty`` would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific ``Host column`` value than the ``'monty'@'%'`` account and thus comes earlier in the user table sort order. * One account has a username of ``admin`` and no password. This account can be used only by connecting from the local host. It is granted the ``RELOAD`` and ``PROCESS`` administrative privileges. These privileges allow the ``admin`` user to execute the ``mysqladmin reload``, ``mysqladmin refresh``, and ``mysqladmin flush-xxx`` commands, as well as ``mysqladmin processlist`` . No privileges are granted for accessing any databases. You could add such privileges later by issuing additional ``GRANT`` statements. * One account has a username of ``dummy`` and no password. This account can be used only by connecting from the local host. No privileges are granted. The ``USAGE`` privilege in the ``GRANT`` statement enables you to create an account without giving it any privileges. It has the effect of setting all the global privileges to ``'N'``. It is assumed that you will grant specific privileges to the account later. * The statements that create accounts with no password will fail if the ``NO_AUTO_CREATE_USER SQL`` mode is enabled. To deal with this, use an ``IDENTIFIED BY`` clause that specifies a non-empty password. Here's a statement which grants a user ``custom`` with password ``obscure`` access to just one database ``bankaccount`` and only with specific commands:: mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> ON bankaccount.* -> TO 'custom'@'localhost' -> IDENTIFIED BY 'obscure'; As of MySQL 5.0.2, you can remove an account and its privileges as follows:: mysql> DROP USER user; You can find out which privileges a user has with:: mysql> SHOW GRANTS FOR 'root'@'localhost'; To show all grants you could do this:: mysql> SHOW GRANTS; For just the current user's grants:: mysql> SHOW GRANTS FOR CURRENT_USER; You can set passwords like this:: mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit'); Only users such as ``root`` that have update access to the ``mysql`` database can change the password for other users. If you are not connected as an anonymous user, you can change your own password by omitting the ``FOR`` clause:: mysql> SET PASSWORD = PASSWORD('biscuit'); .. note :: Do you have to ``FLUSH PRIVILEGES`` after this?? You can also use a ``GRANT USAGE`` statement at the global level (ON *.*) to assign a password to an account without affecting the account's current privileges:: mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit'; The REVOKE statement enables system administrators to revoke privileges from MySQL accounts:: mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... The privileges you can set are described here: http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html Secure Connections ========================== It is worth noting that data is not encrypted by data between client and server as this is CPU intensive. Find out about secure connections here: http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html Creating Tables ======================== Described completely here: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Further Reading =============================== * http://www.yolinux.com/TUTORIALS/LinuxTutorialMySQL.html * http://www.washington.edu/computing/web/publishing/mysql-install.html * http://fedorasolved.org/server-solutions/installing-configuring-mysql-server * http://www.gentoo.org/doc/en/mysql-howto.xml