Install MySQL in ubuntu (reprint)

MySQL installed in linux ubuntu

The Linux used in this article is an Ubuntu 12.04.2 LTS 64bit system, and the installation of MySQL database packages can be implemented through Apt-get.

Install MySQL database in linux ubuntu

#安装MySQL服务器端~ sudo apt-get install mysql-server

The installation process will pop up the prompt box, enter the root user's password, I am here to set the password for MySQL.

After the installation is complete, the MySQL server will start automatically, we check the MySQL server program

# 检查MySQL服务器系统进程~ ps -aux|grep mysqlmysql     3205  2.0  0.5 549896 44092 ?        Ssl  20:10   0:00 /usr/sbin/mysqldconan     3360  0.0  0.0  11064   928 pts/0    S+   20:10   0:00 grep --color=auto mysql# 检查MySQL服务器占用端口~ netstat -nlt|grep 3306tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN# 通过启动命令检查MySQL服务器状态~ sudo /etc/init.d/mysql statusRather than invoking init scripts through /etc/init.d, use the service(8)utility, e.g. service mysql statusSince the script you are attempting to invoke has been converted to anUpstart job, you may also use the status(8) utility, e.g. status mysqlmysql start/running, process 3205# 通过系统服务命令检查MySQL服务器状态~ service mysql statusmysql start/running, process 3205

3. Accessing MySQL from a command-line client

Installing the MySQL server will automatically install the MySQL command line client program together.

The native input MySQL command can be started and the client program accesses the MySQL server.

~ mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 42Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql>

User name and password, login server

~ mysql -uroot -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 37Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql>

Some simple command operations for MySQL.

# View all Databases mysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | Test |+--------------------+2 rows in Set (0.00 sec) # Switch to Information_schema library mysql> use Information_schema Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changed# View all tables in Information_schema library mysql> show tables;+---------------------------------------+| Tables_in_information_schema |+---------------------------------------+| Character_sets | | collations | | collation_character_set_applicability | | COLUMNS | | Column_privileges | | ENGINES | | EVENTS | | FILES | | Global_status | | Global_variables | |        Key_column_usage              || PARAMETERS | | partitions | | PLUGINS | | Processlist | | PROFILING | | referential_constraints | | ROUTINES | | schemata | | Schema_privileges | | Session_status | | Session_variables | | STATISTICS | | TABLES | | tablespaces | | table_constraints | | Table_privileges | | TRIGGERS | | User_privileges | | views | | Innodb_buffer_page | | Innodb_trx | | Innodb_buffer_pool_stats | | Innodb_lock_waits | | Innodb_cmpmem | | innodb_cmp | | Innodb_locks | | Innodb_cmpmem_reset | | Innodb_cmp_reset | | INNODB_BUFFER_PAGE_LRU |+---------------------------------------+40 rows in Set (0.01 sec) # View the character set encoding of the database mysq L> Show variables like '%char% '; +--------------------------+----------------------------+| variable_name | Value |+--------------------------+----------------------------+| character_set_client | UTF8 | | character_set_connection | UTF8 | | Character_set_database | UTF8 | | Character_set_filesystem | binary | | Character_set_results | UTF8 | | Character_set_server | Latin1 | | Character_set_system | UTF8 | | Character_sets_dir | /usr/share/mysql/charsets/|+--------------------------+----------------------------+8 rows in Set (0.00 sec) 

4. Modify the configuration of the MySQL server

Next, I need to do some configuration to make MySQL conform to the basic development requirements.

4.1 Setting the character encoding to UTF-8

By default, the MySQL character set is latin1, so in the storage of Chinese, there will be garbled situation, so we need to change the character set to UTF-8.

Open the MySQL server configuration file with VI my.cnf

~ sudo vi /etc/mysql/my.cnf#在[client]标签下，增加客户端的字符编码[client]default-character-set=utf8#在[mysqld]标签下，增加服务器端的字符编码[mysqld]character-set-server=utf8collation-server=utf8_general_ci

4.2 Let MySQL server be accessed remotely

By default, the MySQL server does not allow remote access and allows only native access, so we need to set the ability to turn on remote access.

Open the MySQL server configuration file with VI my.cnf

~ sudo vi /etc/mysql/my.cnf#注释bind-address#bind-address            = 127.0.0.1

After modifying, restart the MySQL server.

~ sudo /etc/init.d/mysql restartRather than invoking init scripts through /etc/init.d, use the service(8)utility, e.g. service mysql restartSince the script you are attempting to invoke has been converted to anUpstart job, you may also use the stop(8) and then start(8) utilities,e.g. stop mysql ; start mysql. The restart(8) utility is also available.mysql start/running, process 3577

Re-Login Server

~ Mysql-uroot-penter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 37Server version:5.5.35-0ubuntu0.12.04.2 (Ubuntu) Copyright (c) +, Oracle and/o R its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.# again to see the string encoding mysql> show variables like '%char% '; +------------------------ --+----------------------------+| variable_name | Value |+--------------------------+----------------------------+| character_set_client | UTF8 | | character_set_connection | UTF8 | | Character_set_database | UTF8 | | Character_set_filesystem | binary | | Character_set_results | UTF8 | | Character_set_server |       Utf8                || Character_set_system | UTF8 | | Character_sets_dir | /usr/share/mysql/charsets/|+--------------------------+----------------------------+8 rows in Set (0.00 sec)

We check MySQL's Network listening port

# 检查MySQL服务器占用端口~ netstat -nlt|grep 3306  tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

We saw from the network between the listening from 127.0.0.1:3306 to 0 0.0.0.0:3306, indicating that MySQL has allowed remote login access. Remote access through the root account is a very insecure operation, so we will next create a new database and create a new user for remote access.

5. Create a new database and set up an access account

Log in to MySQL server via ROOT account

 ~ Mysql-uroot-penter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 39Server version:5.5.35-0ubuntu0.12.04.2 (Ubuntu) Copyright (c) +, Oracle and/o R its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.# create a database abc;# using the database abcmysql> use Abc;databas abcmysql> e changed# in database ABC, create a new Table a1mysql> CREATE TABLE a1 (ID int primary key,name varchar (+) not NULL); Query OK, 0 rows affected (0.05 sec) # New book user, password is book, allows book remote access to the ABC database, authorized book for all databases of ABC mysql> grant all on abc.* to [email protected] '% ' identified by ' book '; Query OK, 0 rows Affected (0.00 sec) #允许book可以本地访问abc数据库, authorize book for all database mysql> grant all on abc.* to [email prote CTED] identified by ' book '; Query OK, 0 rows Affected (0.00 sec) 

We use the book user login locally

# 使用book用户登陆~ mysql -ubook -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 40Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.#进行abc数据库mysql> use abc;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed#查看abc数据库的表mysql> show tables;+---------------+| Tables_in_abc |+---------------+| a1            |+---------------+1 row in set (0.00 sec)

We use the book user login on another Linux remote

~ mysql -ubook -p -h 192.168.1.199Enter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 41Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> use abcReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+---------------+| Tables_in_abc |+---------------+| a1            |+---------------+1 row in set (0.00 sec)

5. Change the data storage location

Sometimes we may also need to change the location of the MySQL data store, one way is to modify the configuration file/etc/mysql/my.cnf directly, find the DataDir property to modify the directory.

~ vi /etc/mysql/my.cnf[mysqld]datadir         = /var/lib/mysql

If this method is modified, then other calls to the location of the storage path, we also need to make changes, such as the use of the/usr/bin/mysql_install_db command, the file Ldata properties need to be modified, about mysql_install_db The use of commands can be referenced in the article, [MySQL optimizer] ibdata1 for MySQL data files.

There is another way to modify the storage location, which is done through the soft connection (ln-s) of the Linux system. When we mount a new hard drive, stop the MySQL service, and then move the/var/lib/mysql directory to the new hard disk storage, a soft connection to the new location is established at/var/lib/mysql.

# 停止MySQL服务器~ /etc/init.d/mysql stop# 挂载硬盘~ mount -t ext4 /dev/vdb1 /vdb1# 建立新存储目录~ mkdir /vdb1/data# 移动MySQL数据目录到新目录~ mv /var/lib/mysql /vdb1/data/# 软连接~ ln -s /vdb1/data/mysql /var/lib/mysql

Modify the alias definition file for AppArmor

~ vi /etc/apparmor.d/tunables/aliasalias /var/lib/mysql/ -> /vdb1/data/mysql/,

Note: If you do not modify the configuration of AppArmor, MySQL will not start, and always prompt is the issue of permissions.

# 重启apparmor服务~ /etc/init.d/apparmor restart# 重启MySQL服务器~ /etc/init.d/mysql start

This completes the MySQL data storage location modification.

Through the above operation, we will put the MySQL database server in Linux Ubuntu system installation completed.

The source of this article: http://blog.fens.me/linux-mysql-install/

Install MySQL in ubuntu (reprint)