MySQL start and stop
一、本文说明
本实验主要是演示MySQL的四种启动方式，附带停止的操作。
二、mysqld
mysqld is the MySQL server mysqld reads options from the [mysqld] and [server] groups we can invoke mysqld manually.This is usually not done except for debugging purposes.If you invoke the server this way,error messages go to the terminal by default rather than to the error log.
connection
start
./bin/mysqld --defaults-file=./my.cnf --user=mysql
connect
mysql --defaults-file=./my.cnf
mysql -S /tmp/mysql.sock
----启动： [root@node4 mysql5.5]# ./bin/mysqld --defaults-file=./my.cnf --user=mysql & [1] 11315 [root@node4 mysql5.5]# 130729 0:50:34 InnoDB: The InnoDB memory heap is disabled 130729 0:50:34 InnoDB: Mutexes and rw_locks use InnoDB's own implementation 130729 0:50:34 InnoDB: Compressed tables use zlib 1.2.3 130729 0:50:34 InnoDB: Initializing buffer pool, size = 128.0M 130729 0:50:34 InnoDB: Completed initialization of buffer pool 130729 0:50:34 InnoDB: highest supported file format is Barracuda. 130729 0:50:34 InnoDB: Waiting for the background threads to start 130729 0:50:35 InnoDB: 1.1.8 started; log sequence number 1595675 130729 0:50:36 [Note] Event Scheduler: Loaded 0 events 130729 0:50:36 [Note] ./bin/mysqld: ready for connections. Version: '5.5.22-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution [root@node4 mysql5.5]# ps -ef |grep mysql |grep -v grep mysql 11315 5333 1 00:50 pts/1 00:00:00 ./bin/mysqld --defaults-file=./my.cnf --user=mysql ----连接： [root@node4 mysql5.5]# mysql --defaults-file=./my.cnf [root@node4 mysql5.5]# mysql -S /tmp/mysql.sock ----关闭： [root@node4 mysql5.5]# mysqladmin shutdown
三、mysqld_safe
mysqld_safe reads all options the [mysqld],[server],and [mysqld_safe] sections in option files.For backward compatibility,it also reads [safe_mysqld] sections,although you should rename such sections to [mysqd_safe] in MySQL 5.1 installations. mysqld_safe is a shell script that invokes mysqld.The script sets up the error log,and then launches mysqld and monitors it.If mysqld terminates abnormally,mysqld_safe restarts it.
connection
start
mysqld_safe --defaults-file=./my.cnf --user=mysql &
connect
mysql --defaults-file=./my.cnf
mysql -S /tmp/mysql.sock
----启动： [root@node4 mysql5.5]# ./bin/mysqld_safe --default-file=./my.cnf --user=mysql & [1] 11352 [root@node4 mysql5.5]# 130729 00:54:22 mysqld_safe Logging to '/opt/mysql5.5/data/node4.err'. 130729 00:54:23 mysqld_safe Starting mysqld daemon with databases from /opt/mysql5.5/data 130729 00:54:25 mysqld_safe mysqld from pid file /opt/mysql5.5/data/node4.pid ended [1]+ Done ./bin/mysqld_safe --default-file=./my.cnf --user=mysql ----连接： [root@node4 mysql5.5]# mysql --defaults-file=./my.cnf [root@node4 mysql5.5]# mysql -S /tmp/mysql.sock ----关闭： [root@node4 mysql5.5]# mysqladmin shutdown
四、mysql.server
MySQL distributions on Unix include a script named mysql.server.It can be used on
systems such as Linux and Solaris that use System V-style run directaries to start and stop
system services.It is also used by the Max OS X Startup Item for MySQL.
$MySQL_BASE/share/mysql/mysql.server and support-files/mysql.server
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.1
a shell script that invoke mysqld_safe.
connection
start
cd share/mysql
./mysql.server start
cp ./mysql.server /etc/rc.d/init.d/mysql
chkconfig --add mysql
service mysql start
connect
mysql --default-file=./my.cnf
mysql -S /tmp/mysql.sock
----启动： [root@node4 mysql5.5]# ./support-files/mysql.server start Starting MySQL... [ OK ] [root@node4 mysql5.5]# ps -ef |grep mysql |grep -v grep root 11873 1 1 01:01 pts/1 00:00:00 /bin/sh /opt/mysql5.5/bin/mysqld_safe --datadir=/opt/mysql5.5/data --pid-file=/opt/mysql5.5/data/node4.pid mysql 12329 11873 4 01:01 pts/1 00:00:00 /opt/mysql5.5/bin/mysqld --basedir=/opt/mysql5.5 --datadir=/opt/mysql5.5/data --plugin-dir=/opt/mysql5.5/lib/plugin --user=mysql --log-error=/opt/mysql5.5/data/node4.err --pid-file=/opt/mysql5.5/data/node4.pid --socket=/tmp/mysql.sock --port=3306 ----设置随系统启动而启动MySQL [root@node4 mysql5.5]# cp ./support-files/mysql.server /etc/init.d/mysql [root@node4 mysql5.5]# chkconfig --add mysql [root@node4 mysql5.5]# chkconfig --list mysql mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off [root@node4 mysql5.5]# service mysql start Starting MySQL.. [ OK ] [root@node4 mysql5.5]# ps -ef |grep mysql |grep -v grep root 12423 1 1 01:03 pts/1 00:00:00 /bin/sh /opt/mysql5.5/bin/mysqld_safe --datadir=/opt/mysql5.5/data --pid-file=/opt/mysql5.5/data/node4.pid mysql 12881 12423 3 01:03 pts/1 00:00:00 /opt/mysql5.5/bin/mysqld --basedir=/opt/mysql5.5 --datadir=/opt/mysql5.5/data --plugin-dir=/opt/mysql5.5/lib/plugin --user=mysql --log-error=/opt/mysql5.5/data/node4.err --pid-file=/opt/mysql5.5/data/node4.pid --socket=/tmp/mysql.sock --port=3306 ---连接： [root@node4 mysql5.5]# mysql --defaults-file=./my.cnf [root@node4 mysql5.5]# mysql -S /tmp/mysql.sock ----关闭： [root@node4 mysql5.5]# mysqladmin shutdown [root@node4 mysql5.5]# service mysql stop Shutting down MySQL. [ OK ]
五、mysqld_multi
mysqld_multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP prots.It can start or stop servers,or report their current status.The MySQL Instance Manager is an alternative means of managing multiple servers
how to config
1). mkdir $MySQL_BASE/data2
2). mysql_install_db --basedir=$mysql_base --datadir=$mysql_base/data2 --user=mysql
3). vi $MySQL_BASE/my.cnf
[mysqld_multi]
mysqld = /opt/mysql5152/bin/mysqld_safe
mysqladmin = /opt/mysql5152/bin/mysqladmin
user = test
password = test
[mysqld3306]
.
.
.
4). mysqld_multi --defaults-file=./my.cnf start 3306
5). mysql -uroot -P 3306 --protocol=tcp
mysql -S /tmp/mysql3306.sock
6). GRANT SHUTDOWN ON *.* TO 'test'@'localhost' IDENTIFIED BY 'test';
7). mysqld_multi --defaults-file=./my.cnf stop 3306
----创建存放数据库的目录 [root@node4 mysql5.5]# mkdir data2 ----创建数据库基表 [root@node4 mysql5.5]# ./scripts/mysql_install_db --basedir=/opt/mysql5.5 --datadir=/opt/mysql5.5/data2 --user=mysql Installing MySQL system tables... 130729 1:35:52 [Warning] You need to use --log-bin to make --binlog-format work. OK Filling help tables... 130729 1:35:52 [Warning] You need to use --log-bin to make --binlog-format work. 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: /opt/mysql5.5/bin/mysqladmin -u root password 'new-password' /opt/mysql5.5/bin/mysqladmin -u root -h node4 password 'new-password' Alternatively you can run: /opt/mysql5.5/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 /opt/mysql5.5 ; /opt/mysql5.5/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /opt/mysql5.5/mysql-test ; perl mysql-test-run.pl Please report any problems with the /opt/mysql5.5/scripts/mysqlbug script! ----修改data2目录的属主 [root@node4 mysql5.5]# chown -R mysql:mysql data2/ ----修改my.conf文件，添加下面的内容 [root@node4 mysql5.5]# more my.cnf [mysqld_multi] mysqld = /opt/mysql5.5/bin/mysqld_safe mysqladmin = /opt/mysql5.5/bin/mysqladmin user = test password = test # The MySQL server [mysqld3306] port = 3306 socket = /tmp/mysql3306.sock pid-file = /tmp/mysql3306.pid skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M basedir = /opt/mysql5.5 datadir = /opt/mysql5.5/data log-error = /tmp/mysql3306.err [mysqld3307] port = 3307 socket = /tmp/mysql3307.sock pid-file = /tmp/mysql3307.pid skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M basedir = /opt/mysql5.5 datadir = /opt/mysql5.5/data2 log-error = /tmp/mysql3307.err ----启动3306 [root@node4 mysql5.5]# ./bin/mysqld_multi --defaults-file=./my.cnf start 3306 & [1] 13184 [root@node4 mysql5.5]# ps -ef |grep 3306 |grep -v grep root 13192 1 0 01:52 pts/1 00:00:00 /bin/sh /opt/mysql5.5/bin/mysqld_safe --port=3306 --socket=/tmp/mysql3306.sock --pid-file=/tmp/mysql3306.pid --skip-external-locking --key_buffer_size=16M --max_allowed_packet=1M --table_open_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K --read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --basedir=/opt/mysql5.5 --datadir=/opt/mysql5.5/data --log-error=/tmp/mysql3306.err mysql 13637 13192 1 01:52 pts/1 00:00:00 /opt/mysql5.5/bin/mysqld --basedir=/opt/mysql5.5 --datadir=/opt/mysql5.5/data --plugin-dir=/opt/mysql5.5/lib/plugin --user=mysql --skip-external-locking --key-buffer-size=16M --max-allowed-packet=1M --table-open-cache=64 --sort-buffer-size=512K --net-buffer-length=8K --read-buffer-size=256K --read-rnd-buffer-size=512K --myisam-sort-buffer-size=8M --log-error=/tmp/mysql3306.err --pid-file=/tmp/mysql3306.pid --socket=/tmp/mysql3306.sock --port=3306 [1]+ Done ./bin/mysqld_multi --defaults-file=./my.cnf start 3306 ----启动3307 [root@node4 mysql5.5]# ./bin/mysqld_multi --defaults-file=./my.cnf start 3307 & [1] 13658 [root@node4 mysql5.5]# ps -ef |grep 3307 |grep -v grep root 13666 1 1 01:53 pts/1 00:00:00 /bin/sh /opt/mysql5.5/bin/mysqld_safe --port=3307 --socket=/tmp/mysql3307.sock --pid-file=/tmp/mysql3307.pid --skip-external-locking --key_buffer_size=16M --max_allowed_packet=1M --table_open_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K --read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --basedir=/opt/mysql5.5 --datadir=/opt/mysql5.5/data2 --log-error=/tmp/mysql3307.err --log-bin=mysql-bin --binlog_format=mixed --server-id=1 mysql 14156 13666 11 01:53 pts/1 00:00:00 /opt/mysql5.5/bin/mysqld --basedir=/opt/mysql5.5 --datadir=/opt/mysql5.5/data2 --plugin-dir=/opt/mysql5.5/lib/plugin --user=mysql --skip-external-locking --key-buffer-size=16M --max-allowed-packet=1M --table-open-cache=64 --sort-buffer-size=512K --net-buffer-length=8K --read-buffer-size=256K --read-rnd-buffer-size=512K --myisam-sort-buffer-size=8M --log-bin=mysql-bin --binlog-format=mixed --server-id=1 --log-error=/tmp/mysql3307.err --pid-file=/tmp/mysql3307.pid --socket=/tmp/mysql3307.sock --port=3307 [1]+ Done ./bin/mysqld_multi --defaults-file=./my.cnf start 3307 ----对数据库的连接 [root@node4 mysql5.5]# mysql -S /tmp/mysql3306.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.22 Source distribution Copyright (c) 2000, 2011, 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> exit; Bye [root@node4 mysql5.5]# mysql -S /tmp/mysql3307.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, 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> exit; Bye ----关闭3306，首先需要连接数据库，创建test用户并授予它权限，最后使用mysqld_multi关闭3306 [root@node4 mysql5.5]# mysql -S /tmp/mysql3306.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.22 Source distribution Copyright (c) 2000, 2011, 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> GRANT SHUTDOWN ON *.* TO 'test'@'localhost' identified by 'test' with grant option; Query OK, 0 rows affected (0.11 sec) mysql> exit; Bye [root@node4 mysql5.5]# ./bin/mysqld_multi --defaults-file=./my.cnf stop 3306 [root@node4 mysql5.5]# ps -ef |grep 3306 |grep -v grep
