这两个礼拜一直困扰我的一个问题是mysql的处理能力问题,不知道是有些配置没有弄好,还是真的mysql在多cpu、大内存下不能充分利用资源,使得我想通过多个实例来充分利用系统资源。每天上班都是一堆烂事,周末抽空试了一下:
1 利用mysql_install_db生成数据库(这里也可以拷贝一份原来的数据库)
[root@testdb2 mysql]# mysql_install_db --datadir=/var/lib/mysql2 --user=mysql
Installing MySQL system tables...
110903 10:34:51 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK
Filling help tables...
2 编辑mysqld_multi配置文件
可以用mysqld_multi –example来生成一个样例
[root@testdb2 mysql2]# mysqld_multi --example
# This is an example of a my.cnf file for mysqld_multi.
# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf
#
# SOME IMPORTANT NOTES FOLLOW:
#
# 1.COMMON USER
#
# Make sure that the MySQL user, who is stopping the mysqld services, has
# the same password to all MySQL servers being accessed by mysqld_multi.
# This user needs to have the 'Shutdown_priv' -privilege, but for security
# reasons should have no other privileges. It is advised that you create a
# common 'multi_admin' user for all MySQL servers being controlled by
# mysqld_multi. Here is an example how to do it:
#
# GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'password'
#
# You will need to apply the above to all MySQL servers that are being
# controlled by mysqld_multi. 'multi_admin' will shutdown the servers
# using 'mysqladmin' -binary, when 'mysqld_multi stop' is being called.
#
# 2.PID-FILE
#
# If you are using mysqld_safe to start mysqld, make sure that every
# MySQL server has a separate pid-file. In order to use mysqld_safe
# via mysqld_multi, you need to use two options:
#
# mysqld=/path/to/mysqld_safe
# ledir=/path/to/mysqld-binary/
#
# ledir (library executable directory), is an option that only mysqld_safe
# accepts, so you will get an error if you try to pass it to mysqld directly.
# For this reason you might want to use the above options within [mysqld#]
# group directly.
#
# 3.DATA DIRECTORY
#
# It is NOT advised to run many MySQL servers within the same data directory.
# You can do so, but please make sure to understand and deal with the
# underlying caveats. In short they are:
# - Speed penalty
# - Risk of table/data corruption
# - Data synchronising problems between the running servers
# - Heavily media (disk) bound
# - Relies on the system (external) file locking
# - Is not applicable with all table types. (Such as InnoDB)
# Trying so will end up with undesirable results.
#
# 4.TCP/IP Port
#
# Every server requires one and it must be unique.
#
# 5.[mysqld#] Groups
#
# In the example below the first and the fifth mysqld group was
# intentionally left out. You may have 'gaps' in the config file. This
# gives you more flexibility.
#
# 6.MySQL Server User
#
# You can pass the user=... option inside [mysqld#] groups. This
# can be very handy in some cases, but then you need to run mysqld_multi
# as UNIX root.
#
# 7.A Start-up Manage Script. for mysqld_multi
#
# In the recent MySQL distributions you can find a file called
# mysqld_multi.server.sh. It is a wrapper for mysqld_multi. This can
# be used to start and stop multiple servers during boot and shutdown.
#
# You can place the file in /etc/init.d/mysqld_multi.server.sh and
# make the needed symbolic links to it from various run levels
# (as per Linux/Unix standard). You may even replace the
# /etc/init.d/mysql.server script. with it.
#
# Before using, you must create a my.cnf file either in /etc/my.cnf
# or /root/.my.cnf and add the [mysqld_multi] and [mysqld#] groups.
#
# The script. can be found from support-files/mysqld_multi.server.sh
# in MySQL distribution. (Verify the script. before using)
下面是我的:
[oracle@testdb2 ~]$ cat /etc/mysqld_multi.cnf
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
#user = multi_admin
#password = my_password
[client]
#password = your_password
#port = 3306
#socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld1]
port = 3306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/hostname.pid
datadir = /var/lib/mysql
skip-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
#log-bin=/var/lib/mysql/mysql-bin
server-id = 1
wait_timeout=28800000
innodb_data_file_path = ibdata1:66M;ibdata2:2000M;ibdata3:10M:autoextend
innodb_log_file_size = 200M
innodb_log_files_in_group = 4
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 120M
innodb_log_buffer_size = 16M
[mysqld2]
port = 3307
socket = /var/lib/mysql2/mysql2.sock
pid-file = /var/lib/mysql2/hostname2.pid
datadir = /var/lib/mysql2
skip-locking
key_buffer_size = 128M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
log-bin=/var/lib/mysql2/mysql-bin
server-id = 2
wait_timeout=28800000
innodb_log_file_size = 100M
innodb_log_files_in_group = 3
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 120M
innodb_log_buffer_size = 16M
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
3 用mysqld_multi来控制:
[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start
[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf stop
start后面跟具体的数字,比如start 1,则单独开启mysqld1这个实例。
[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is running
[root@testdb2 ~]#
[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start 1
[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
4 操作,通过mysql连接注意要制定端口和socket:
[root@testdb2 mysql]# mysql -u root -P3307 -S/var/lib/mysql2/mysql2.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.1.51, for unknown-linux-gnu (x86_64) using readline 5.1
Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.51-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql2/mysql2.sock
Uptime: 1 min 27 sec
Threads: 1 Questions: 5 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.57
--------------
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
5 操作系统可看到两个mysqld进程:
root 17474 1 0 10:54 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --port=3306 --socket=/var/lib/mysql/mysql.sock
root 17480 1 0 10:54 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --port=3307 --socket=/var/lib/mysql2/mysql.sock
mysql 17952 17474 0 10:54 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/
mysql 17957 17480 0 10:54 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/758322/viewspace-706618/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/758322/viewspace-706618/