记录一下mysql在centos6.6上的安装与配置。

安装

$ yum install -y mysql-server mysql mysql-deve

启动服务

$ service mysqld start

提示错误：

mysqld starten:                                            [FEHLGESCHLAGEN]

因为需要sudo，第一次启动会进行初始化：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
$ sudo service mysqld start
MySQL-Datenbank initialisieren:  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/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
/usr/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 ; /usr/bin/mysqld_safe &

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

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
mysqld starten:                                            [  OK  ]

其中有一段：

1
2
3
4
5
PLEASE 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 localhost.localdomain password 'new-password'

这是提示我们设置默认密码的。照做即可。

然后登陆mysql：

$ mysql -u root -p

mac下安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ brew install mysql

...

We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

To connect run:
    mysql -uroot

To have launchd start mysql at login:
  ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents
Then to load mysql now:
  launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
Or, if you don't want/need launchctl, you can just run:
  mysql.server start

启动

mysql官方推荐使用mysqld_safe来启动mysqld服务，这个脚本做了更多的工作，包括打日志，遇到错误自动重启mysql等。

mysqld_safe --user=mysql &

不过我在mysql文档中没有找到如何使用mysqld_safe来关闭mysqld服务。

启动、停止、重启 MySQL 常见的操作方法这篇文章中提到了几种停止mysql的方法：

  • 使用 service 启动：service mysqld stop
  • 使用 mysqld 脚本启动：/etc/inint.d/mysqld stop
  • mysqladmin shutdown

我选择的是mysqladmin shutdown。

手动安装mysql，使用mysqld_safe启动mysql服务这篇文字提供了一个脚本，有参考意义：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
#!/bin/sh

mysql_port=3306
mysql_username="root"
mysql_password=""

function_start_mysql()
{
    printf "Starting MySQL...\n"
    /bin/sh /usr/local/webserver/mysql/bin/mysqld_safe --defaults-file=/data1/mysql/${mysql_port}/my.cnf 2>&1 > /dev/null &
}

function_stop_mysql()
{
    printf "Stoping MySQL...\n"
    /usr/local/webserver/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -h 127.0.0.1 -S /tmp/mysql.sock shutdown
}

function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 5
    function_start_mysql
}

function_kill_mysql()
{
    kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
    kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}

if [ "$1" = "start" ]; then
    function_start_mysql
elif [ "$1" = "stop" ]; then
    function_stop_mysql
elif [ "$1" = "restart" ]; then
function_restart_mysql
elif [ "$1" = "kill" ]; then
function_kill_mysql
else
    printf "Usage: /data1/mysql/${mysql_port}/mysql {star|stop|restart|kill}\n"
fi

自动启动

$ sudo chkconfig mysqld on

虽然不知道原理，但是chkconfig配置的自动启动是使用mysqld_safe来启动服务的，因为启动系统后，查看进程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 系统启动后，有mysqld_safe和mysqld进程，而且mysqld进程的父进程是mysqld_safe，说明是mysqld_safe启动的
$ ps -ef | grep mysql
root      1398     1  0 15:34 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql     3670  1398  0 15:37 ?        00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
vagrant   3732  3391  0 15:37 pts/0    00:00:00 grep mysql

-- 关闭mysqld进程
$ sudo kill -9 3670

-- 关闭mysqld进程后，新的mysqld进程出现了，说明mysqld_safe的自动重启mysqld的功能正在运作
$ ps -ef | grep mysql
root      1398     1  0 15:34 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql     3772  1398  1 15:38 ?        00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
vagrant   3791  3391  0 15:38 pts/0    00:00:00 grep mysql

配置账户

默认mysql只提供了一个root用户。root用户只能从本地登录。我们可以建立一个可以从远程登录的普通用户。

配置账户要注意最小权限原则。

1
2
3
4
-- 先赋予用户最基本的登录权限，用户可以从任何host登录
grant usage on *.* to mushan identified by '111';
-- 授予用户特定库的操作权限
grant all on xx_table.* to mushan;

上面两句话其实等价于一句：

1
grant all on xx_table.* to mushan identified by '111';

参考资料