<服务>MySQL服务

时间：Nov. 22, 2016 分类：Linux服务

MySQL

MySQL是传统的web服务的后台关系型数据库。

安装MySQL

编译安装MySQL

[root@why-2 mysql]# groupadd mysql
[root@why-2 mysql]# useradd mysql -g mysql -M -s /sbin/nologin
[root@why-2 ~]# tar -xf mysql-5.1.72.tar.gz 
[root@why-2 ~]# cd mysql-5.1.72
[root@why-2 ~]# cd mysql-5.1.72
[root@why-2 mysql-5.1.72]# ./configure --prefix=/usr/local/mysql-5.1.72 --with-unix-socket-path=/usr/local/mysql-5.1.72/tmp/mysql.sock --localstatedir=/usr/local/mysql-5.1.72/data --enable-assembler --enable-thread-safe-client --with-mysqld-user=mysql --with-big-tables --without-debug --with-pthread --enable-assembler --with-extra-charsets=complex --with-readline --with-ssl --with-embedded-server --enable-local-infile --with-plugins=partition,innobase --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static

可能会遇到的问题

如果遇到了curses/termcap的错误，说明没装ncurses-devel

编译安装MySQL make && make install

[root@why-2 mysql-5.1.72]# make && make install
[root@why-2 ~]# ln -s /usr/local/mysql-5.1.72/ /usr/local/mysql

make的时候可以加 -j 4参数，代表有四个cpu

如果不是做MySQL服务器，安装就算完成。
如果是做MySQL服务器需要继续。

MySQL 配置文件

[root@why-2 support-files]# ll |grep ".cnf$"
-rw-r--r-- 1 root root   4742 11月  2 18:10 my-huge.cnf
-rw-r--r-- 1 root root  19777 11月  2 18:10 my-innodb-heavy-4G.cnf
-rw-r--r-- 1 root root   4716 11月  2 18:10 my-large.cnf
-rw-r--r-- 1 root root   4727 11月  2 18:10 my-medium.cnf
-rw-r--r-- 1 root root   2495 11月  2 18:10 my-small.cnf

根据硬件配置从小到大可选配置文件，medium，small，large，huge，heavy

[root@why-2 support-files]# cp /root/mysql-5.1.72/support-files/my-small.cnf /etc/my.cnf 
cp：是否覆盖"/etc/my.cnf"？ y

生成数据存放目录

创建存放目录

[root@why-2 support-files]# mkdir -p /usr/local/mysql/data

修改目录权限

[root@why-2 support-files]# chown -R mysql:mysql !$/..
chown -R mysql:mysql /usr/local/mysql/data/..
[root@why-2 mysql]# ll !$/data
ll /usr/local/mysql/data/../data
总用量 0

创建数据库

[root@why-2 mysql]# ./usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql
Installing MySQL system tables...
161102 18:50:52 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK
Filling help tables...
161102 18:50:52 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h why-2 password 'new-password'

Alternatively you can run:
/usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/local/mysql/scripts/mysqlbug script!

查看数据存放目录

[root@why-2 bin]# ll ../data/
总用量 8
drwx------ 2 mysql root 4096 11月  2 18:50 mysql
drwx------ 2 mysql root 4096 11月  2 18:50 test

启动MySQL

启动方式1

[root@why-2 bin]# /usr/local/mysql/bin/mysqld_safe &
[1] 14818
[root@why-2 bin]# 161102 19:02:23 mysqld_safe Logging to '/usr/local/mysql-5.1.72/data/why-2.err'.
161102 19:02:23 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql-5.1.72/data

这种启动方式需要使用mysqladmin shutdown关闭

[root@why-2 bin]# netstat -lnptu|grep mysqld
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      14926/mysqld

启动方式2

拷贝启动脚本

[root@why-2 bin]# cp  ~/mysql-5.1.72/support-files/mysql.server /etc/init.d/mysqld
[root@why-2 bin]# chkconfig --add mysqld
[root@why-2 bin]# chkconfig mysqld on
[root@why-2 bin]# chmod +x /etc/init.d/mysqld
[root@why-2 bin]# vi /etc/init.d/mysqld         #制定程序目录和数据存放目录
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

启动

[root@why-2 bin]# service mysqld start

MySQL操作

[root@why-2 bin]# /usr/local/mysql/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.72 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

配置一下环境变量

[root@why-2 bin]# vi /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
[root@why-2 bin]# . !$

设置密码

[root@why-2 mysql]# /usr/local/mysql/bin/mysqladmin -u root password '123456'
[root@why-2 mysql]# mysql -uroot -p'123456'

查看版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.72    |
+-----------+
1 row in set (0.00 sec)

查看当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

查看主机对应主机名

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
|      | localhost |
| root | localhost |
|      | why-2     |
| root | why-2     |
+------+-----------+
5 rows in set (0.00 sec)

一般为了安全起见，会删掉多余的用户

mysql> drop user ""@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> drop user ""@"why-2";
Query OK, 0 rows affected (0.00 sec)

mysql> drop user "root"@"why-2";
Query OK, 0 rows affected (0.00 sec)

更新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

只保留root@127.0.0.1和root@localhost
mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)

如果误删多了，可以通过bin/mysqld_safe --skip-grant-table &忽略授权表，进入mysql中updata user password=PASSWORD("123456") where

二进制安装MySQL

5.5的版本开始就要通过cmake，make，make install，需要先安装cmake

[root@why-1 ~]# useradd mysql -s /sbin/nologin -M
[root@why-1 ~]# cp mysql-5.5.32-linux2.6-x86_64.tar.gz /usr/local/
[root@why-1 ~]# cd !$
cd /usr/local/
[root@why-1 local]# tar xf mysql-5.5.32-linux2.6-x86_64.tar.gz
[root@why-1 local]# ln -s mysql-5.5.32-linux2.6-x86_64 mysql
[root@why-1 local]# cd mysql
[root@why-1 mysql]# ./scripts/mysql_install_db --user mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h why-1 password 'new-password'

Alternatively you can run:
/usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/local/mysql/scripts/mysqlbug script!

[root@why-1 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@why-1 mysql]# vi !$
vi /etc/init.d/mysqld

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

[root@why-1 mysql]# cp support-files/my-small.cnf /etc/my.cnf
[root@why-1 mysql]# service mysqld start
Starting MySQL...                                          [确定]

在环境变量中添加mysql/bin路径，或者将其下文件拷到环境变量的目录下

[root@why-1 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | localhost |
| root | localhost |
|      | why-1     |
| root | why-1     |
+------+-----------+
6 rows in set (0.00 sec)

mysql> drop user ''@'why-1';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user ''@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'root'@'::1';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'root'@'why-1';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)

如果有删不掉的，可以通过delect方式删除。
可能会遇到libaio.so不存在的问题，可以通过yum install -y libaio-devel的方式安装。
tmp目录不能创建文件，chmod -R 4777 /tmp
mysql的日志一般位于mysql/data下

[root@why-1 mysql]# ./bin/mysqladmin -u root password '123456'
[root@why-1 mysql]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

火眼征信大数据工程师闫大佬