Ubuntu安装使用MySQL

安装配置MySQL最好的方法是查看官方文档！

安装步骤：http://dev.mysql.com/doc/refman/5.5/en/installing.html

Installation of MySQL generally follows the steps outlined here:

1. Determine whether MySQL runs and is supported on your platform.
2. Choose which distribution to install.
3. Download the distribution that you want to install.
4. Install the distribution.
5. Perform any necessary postinstallation setup.

       After installing MySQL, seeSection 2.10, “Postinstallation Setup and Testing” for information about making sure the MySQL server is working properly. Also refer to the information provided inSection 2.10.2, “Securing the Initial MySQL Accounts”. This section describes how to secure the initial MySQL user accounts,which have no passwords until you assign passwords. The section applies whether you install MySQL using a binary or source distribution.

本文采取本地包管理器安装，MySQL5.5，然后进行必要设置

安装5.5：http://dev.mysql.com/doc/refman/5.5/en/linux-installation-native.html

Debian, Ubuntu, Kubuntu

On Debian and related distributions, there are two packages, mysql-client and mysql-server, for the client and server components respectively. You should specify an explicit version, for examplemysql-client-5.1, to ensure that you install the version of MySQL that you want.

To download and install, including any dependencies, use the apt-get command, specifying the packages that you want to install.

Note
Before installing, make sure that you update your apt-get index files to ensure you are downloading the latest available version.

root-shell> apt-get install mysql-client-5.1 mysql-server-5.1

Note
The apt-get command will install a number of packages, including the MySQL server, in order to provide the typical tools and application environment. This can mean that you install a large number of packages in addition to the main MySQL package.

During installation, the initial database will be created, and you will be prompted for the MySQL root password (and confirmation). A configuration file will have been created in/etc/mysql/my.cnf. An init script will have been created in/etc/init.d/mysql.

The server will already be started. You can manually start and stop the server using:

root-shell> service mysql [start|stop]

The service will automatically be added to the 2, 3 and 4 run levels, with stop scripts in the single, shutdown and restart levels.

安装信息：Table 2.12 MySQL Installation Layout for Linux RPM Packages

   http://dev.mysql.com/doc/refman/5.5/en/linux-installation-rpm.html

DirectoryContents of Directory/usr/binClient programs and scripts/usr/sbinThe mysqld server/var/lib/mysqlLog files, databases/usr/share/infoManual in Info format/usr/share/manUnix manual pages/usr/include/mysqlInclude (header) files/usr/lib/mysqlLibraries/usr/share/mysqlMiscellaneous support files, including error messages, character set files, sample configuration files, SQL for databaseinstallation/usr/share/sql-benchBenchmarks
安装后的设置：http://dev.mysql.com/doc/refman/5.5/en/postinstallation.html

安装后过程:  2.10.1 Postinstallation Procedures for Unix-like Systems：http://dev.mysql.com/doc/refman/5.5/en/unix-postinstallation.html

    2.10.1.1 Problems Running mysql_install_db: http://dev.mysql.com/doc/refman/5.5/en/mysql-install-db-problems.html

    The purpose of the mysql_install_db program is to generate new MySQL privilege tables. It does not overwrite existing MySQL privilege tables, and it does not affect any other data.

sudo service mysql stopsudo mv /var/lib/mysql /var/lib/mysql.old

$ sudo mysql_install_db --user=mysqlInstalling MySQL system tables...141006 11:16:23 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.OKFilling help tables...141006 11:16:25 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !To do so, start the server, then issue the following commands:/usr/bin/mysqladmin -u root password 'new-password'/usr/bin/mysqladmin -u root -h <code class="literal">myhost.example.com</code> password 'new-password'Alternatively you can run:/usr/bin/mysql_secure_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default.  This isstrongly recommended for production servers.See the manual for more instructions.You can start the MySQL daemon with:cd /usr ; /usr/bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.plcd /usr/mysql-test ; perl mysql-test-run.plPlease report any problems at http://bugs.mysql.com/

$ sudo service mysql startmysql start/running, process 4579$ sudo mysqladmin reload 

    2.10.1.2 Starting and Stopping MySQL Automatically: http://dev.mysql.com/doc/refman/5.5/en/automatic-start.html

    If you use the Linux server RPM package (MySQL-server-VERSION.rpm), or anative Linux package installation, themysql.server script may be installed in the/etc/init.d directory with the namemysql.

    Some vendors provide RPM packages that install a startup script under a different name such asmysqld.

    mysql.server reads options from the[mysql.server] and[mysqld] sections of option files. For backward compatibility, it also reads[mysql_server] sections, although you should rename such sections to[mysql.server] when using MySQL 5.5. [http://dev.mysql.com/doc/refman/5.5/en/mysql-server.html]  

 没有练习，略过。

    2.10.1.3 Starting and Troubleshooting the MySQL Serverhttp://dev.mysql.com/doc/refman/5.5/en/starting-server.html 

 没有练习，略过。

       After you complete the procedure and have the server running, you should assign passwords to the accounts created bymysql_install_db and perhaps restrict access to test databases. For instructions, seeSection 2.10.2, “Securing the Initial MySQL Accounts”.

   上面完成了mysql_install_db操作，紧接着，要设置密码：

    安全设置：http://dev.mysql.com/doc/refman/5.5/en/default-privileges.html

    Part of the MySQL installation process is to set up the mysql database that contains the grant tables:
    The mysql.user grant table defines the initial MySQL user accounts and their access privileges:
    To display which accounts exist in the mysql.user table and check whether their passwords are empty, use the following statement:

mysql> SELECT User, Host, Password FROM mysql.user;    注意是分号结尾+------+------------+----------+| User | Host       | Password |+------+------------+----------+| root | localhost  |          || root | ovonel-usa |          || root | 127.0.0.1  |          || root | ::1        |          |+------+------------+----------+4 rows in set (0.00 sec)   

   设置密码shell> mysql -u rootmysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('newpwd');mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');   成功之后，再次进入则需要使用密码shell> mysql -u root -pEnter password:

Securing Test Databases

By default, the mysql.db table contains rows that permit access by any user to thetest database and other databases with names that start withtest_. (These rows have an empty User column value, which for access-checking purposes matches any user name.) This means that such databases can be used even by accounts that otherwise possess no privileges. If you want to remove any-user access to test databases, do so as follows:

shell> mysql -u root -pEnter password: (enter root password here)mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%';mysql> FLUSH PRIVILEGES;

The FLUSH statement causes the server to reread the grant tables. Without it, the privilege change remains unnoticed by the server until you restart it.

With the preceding change, only users who have global database privileges or privileges granted explicitly for thetest database can use it.

[http://dev.mysql.com/doc/refman/5.5/en/password-security-user.html]     On Unix, themysql client writes a record of executed statements to a history file (seeSection 4.5.1.3, “mysql Logging”). By default, this file is named .mysql_history and is created in your home directory. Passwords can be written as plain text in SQL statements such asCREATE USER, GRANT, and SET PASSWORD, so if you use these statements, they are logged in the history file. To keep this file safe, use a restrictive access mode, the same way as described earlier for the.my.cnf file.

shell> chmod 600 .mysql_history

[插播]  4.2.6 Using Option Files  [http://dev.mysql.com/doc/refman/5.5/en/option-files.html]

On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first).

File NamePurpose/etc/my.cnfGlobal options/etc/mysql/my.cnfGlobal optionsSYSCONFDIR/my.cnfGlobal options$MYSQL_HOME/my.cnfServer-specific optionsdefaults-extra-fileThe file specified with --defaults-extra-file=path,if any~/.my.cnfUser-specific options

2.10.1 Postinstallation Procedures for Unix-like Systems 的练习

[http://dev.mysql.com/doc/refman/5.5/en/unix-postinstallation.html]

可资参考：https://help.ubuntu.com/12.04/serverguide/mysql.html

配置文件在线生成：https://tools.percona.com

下文的练习没有做，原因是之前安装的方式参考了上面的链接，与2.10.1练习走的过程不太一致，所以没有继续做2/3/4/5/6分步骤中的练习。

In the examples shown here, the server runs under the user ID of themysql login account. This assumes that such an account exists. Either create the account if it does not exist, or substitute the name of a different existing login account that you plan to use for running the server. For information about creating the account, seeCreating a mysql System User and Group, in Section 2.2, “Installing MySQL on Unix/Linux Using Generic Binaries”.

首先创建一个mysql系统用户：

Create a mysql User and Group

[http://dev.mysql.com/doc/refman/5.5/en/binary-installation.html#binary-installation-createsysuser]

If your system does not already have a user and group for mysqld to run as, you may need to create one. The following commands add themysql group and the mysql user. You might want to call the user and group something else instead ofmysql. If so, substitute the appropriate name in the following instructions. The syntax foruseradd and groupadd may differ slightly on different versions of Unix, or they may have different names such asadduser and addgroup.

shell> groupadd mysqlshell> useradd -r -g mysql mysql

Note
Because the user is required only for ownership purposes, not login purposes, theuseradd command uses the -r option to create a user that does not have login permissions to your server host. Omit this option to permit logins for the user (or if youruseradd does not support the option).

如果用户组和用户存在了，照例执行命令会有提示。这里我们继续为mysql账户设置用户密码 [！好像没用！]：

sudo passwd mysql

注意这里的账户密码是系统中mysql用户的密码，而不是数据库中用户的密码(daoba76)，

1. Change location into the top-level directory of your MySQL installation, represented here byBASEDIR:

shell> cd BASEDIR

BASEDIR is the installation directory for your MySQL instance. It is likely to be something like/usr/local/mysql or /usr/local. The following steps assume that you have changed location to this directory.

上面这段话的意思是要你切换目录到mysql的安装目录下，我是使用本地包管理器安装的apt-get install的方式，查找后知道我的bin目录是/usr/bin，而与之对应的scripts也是/usr/bin。好像有错，[https://help.ubuntu.com/12.04/serverguide/mysql.html]此文给出的位置是/var/lib/mysql，实际上跟我电脑上目录一致，就是该目录。

cd /var/lib/mysql

到此目录一看，目录的所有者权限如下，故不做改动（shell>chown -R root .shell> chown -R mysql data），此节练习就不继续了：

$ sudo ls ./mysql -l总用量 28680-rw-rw---- 1 mysql mysql 18874368 Oct  6 17:03 ibdata1-rw-rw---- 1 mysql mysql  5242880 Oct  6 17:03 ib_logfile0-rw-rw---- 1 mysql mysql  5242880 Oct  6 11:25 ib_logfile1drwx------ 2 mysql root      4096 Oct  6 11:16 mysqldrwx------ 2 mysql mysql     4096 Oct  6 11:16 performance_schema

在上面2.10.1.1的练习中曾经对此目录有过改动，原目录情况如下：

$ sudo ls ./mysql.old/ -l总用量 28696-rw-r--r-- 1 mysql root         0 Sep 21 09:34 debian-5.5.flag-rw-rw---- 1 mysql mysql 18874368 Oct  6 11:12 ibdata1-rw-rw---- 1 mysql mysql  5242880 Oct  6 11:12 ib_logfile0-rw-rw---- 1 mysql mysql  5242880 Sep 18 00:32 ib_logfile1drwx------ 2 mysql root      4096 Sep 21 09:34 mysql-rw-rw---- 1 mysql mysql        0 Sep 21 16:59 mysql-bin.index-rw-rw---- 1 mysql mysql    11904 Sep 21 17:05 mysql-error.log-rw-rw---- 1 mysql root         6 Sep 18 00:32 mysql_upgrade_infodrwx------ 2 mysql mysql     4096 Sep 21 09:34 performance_schema

7.Start the MySQL server:没有成功，未完待续！

可资参考：https://help.ubuntu.com/12.04/serverguide/mysql.html

配置文件在线生成：https://tools.percona.com