Percona Server with XtraDB 5.5.21跟handlersocket插件安装

Percona Server with XtraDB 5.5.21和handlersocket插件安装

1.新建MySQL用户和用户组
groupadd mysql
useradd -g mysql mysql

2.新建MySQL数据目录
mkdir -p /data/mysql/data/

mkdir -p /data/mysql/tablespace
mkdir -p /data/mysql/binlog/
mkdir -p /data/mysql/relaylog/
chown -R mysql:mysql /data/mysql/

3.安装MySQL
cd /usr/local/src/
wget http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.21-25.0/binary/linux/i686/Percona-Server-5.5.21-rel25.0-227.Linux.x86_64.tar.gz
tar zvxf Percona-Server-5.5.21-rel25.0-227.Linux.x86_64.tar.gz -C /usr/local/
cd /usr/local/Percona-Server-5.5.21-rel25.0-227.Linux.x86_64/scripts/
./mysql_install_db --basedir=/usr/local/Percona-Server-5.5.21-rel25.0-227.Linux.x86_64 --datadir=/data/mysql/data --user=mysql

4.MySQL的配置文件my.cnf，放在/et/目录

#BEGIN CONFIG INFO#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries#TYPE: SYSTEM#END CONFIG INFO## This is a MySQL example config file for systems with 4GB of memory# running mostly MySQL using InnoDB only tables and performing complex# queries with few connections.# # MySQL programs look for option files in a set of# locations which depend on the deployment platform.# You can copy this option file to one of those# locations. For information about these locations, see:# http://dev.mysql.com/doc/mysql/en/option-files.html## In this file, you can use all long options that a program supports.# If you want to know which options a program supports, run the program# with the "--help" option.## More detailed information about the individual options can also be# found in the manual.### The following options will be read by MySQL client applications.# Note that only client applications shipped by MySQL are guaranteed# to read this section. If you want your own MySQL client program to# honor these values, you need to specify it as an option during the# MySQL client library initialization.#[client]#password= [your_password]# use loose for mysqlbinlog loose-character-set = utf8port= 3306socket= /tmp/mysql.sock# *** Application-specific options follow here ***## The MySQL server#[mysqld]# generic configuration optionscharacter_set_server = utf8user        = mysqlport= 3306socket= /tmp/mysql.sockbasedir     = /usr/local/Percona-Server-5.5.21-rel25.0-227.Linux.x86_64datadir     = /data/mysql/datalog-error   = /data/mysql/mysql_error.logpid-file    = /data/mysql/mysql.pidskip-name-resolveinteractive_timeout = 288000wait_timeout = 288000# relay-logrelay-log-index = /data/mysql/relaylog/relaylogrelay-log-info-file = /data/mysql/relaylog/relaylogrelay-log = /data/mysql/relaylog/relaylogexpire_logs_days = 7# back_log is the number of connections the operating system can keep in# the listen queue, before the MySQL connection manager thread has# processed them. If you have a very high connection rate and experience# "connection refused" errors, you might need to increase this value.# Check your OS documentation for the maximum value of this parameter.# Attempting to set back_log higher than your operating system limit# will have no effect.back_log = 600# Don't listen on a TCP/IP port at all. This can be a security# enhancement, if all processes that need to connect to mysqld run# on the same host.  All interaction with mysqld must be made via Unix# sockets or named pipes.# Note that using this option without enabling named pipes on Windows# (via the "enable-named-pipe" option) will render mysqld useless!#skip-networking# The maximum amount of concurrent sessions the MySQL server will# allow. One of these connections will be reserved for a user with# SUPER privileges to allow the administrator to login even if the# connection limit has been reached.max_connections = 500# Maximum amount of errors allowed per host. If this limit is reached,# the host will be blocked from connecting to the MySQL server until# "FLUSH HOSTS" has been run or the server was restarted. Invalid# passwords and other errors during the connect phase result in# increasing this value. See the "Aborted_connects" status variable for# global counter.max_connect_errors = 6000# The number of open tables for all threads. Increasing this value# increases the number of file descriptors that mysqld requires.# Therefore you have to make sure to set the amount of open files# allowed to at least 4096 in the variable "open-files-limit" in# section [mysqld_safe]table_open_cache = 10240# Enable external file level locking. Enabled file locking will have a# negative impact on performance, so only use it in case you have# multiple database instances running on the same files (note some# restrictions still apply!) or if you use other software relying on# locking MyISAM tables on file level.#external-locking# The maximum size of a query packet the server can handle as well as# maximum query size server can process (Important when working with# large BLOBs).  enlarged dynamically, for each connection.max_allowed_packet = 32M# The size of the cache to hold the SQL statements for the binary log# during a transaction. If you often use big, multi-statement# transactions you can increase this value to get more performance. All# statements from transactions are buffered in the binary log cache and# are being written to the binary log at once after the COMMIT.  If the# transaction is larger than this value, temporary file on disk is used# instead.  This buffer is allocated per connection on first update# statement in transactionbinlog_cache_size = 4M# Maximum allowed size for a single HEAP (in memory) table. This option# is a protection against the accidential creation of a very large HEAP# table which could otherwise use up all memory resources.max_heap_table_size = 512M# Size of the buffer used for doing full table scans.# Allocated per thread, if a full scan is needed.read_buffer_size = 4M# When reading rows in sorted order after a sort, the rows are read# through this buffer to avoid disk seeks. You can improve ORDER BY# performance a lot, if set this to a high value.# Allocated per thread, when needed.read_rnd_buffer_size = 32M# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY# queries. If sorted data does not fit into the sort buffer, a disk# based merge sort is used instead - See the "Sort_merge_passes"# status variable. Allocated per thread if sort is needed.sort_buffer_size = 8M# This buffer is used for the optimization of full JOINs (JOINs without# indexes). Such JOINs are very bad for performance in most cases# anyway, but setting this variable to a large value reduces the# performance impact. See the "Select_full_join" status variable for a# count of full JOINs. Allocated per thread if full join is foundjoin_buffer_size = 8M# How many threads we should keep in a cache for reuse. When a client# disconnects, the client's threads are put in the cache if there aren't# more than thread_cache_size threads from before.  This greatly reduces# the amount of thread creations needed if you have a lot of new# connections. (Normally this doesn't give a notable performance# improvement if you have a good thread implementation.)thread_cache_size = 300# This permits the application to give the threads system a hint for the# desired number of threads that should be run at the same time.  This# value only makes sense on systems that support the thread_concurrency()# function call (Sun Solaris, for example).# You should try [number of CPUs]*(2..4) for thread_concurrencythread_concurrency = 8# Query cache is used to cache SELECT results and later return them# without actual executing the same query once again. Having the query# cache enabled may result in significant speed improvements, if your# have a lot of identical queries and rarely changing tables. See the# "Qcache_lowmem_prunes" status variable to check if the current value# is high enough for your load.# Note: In case your tables change very often or if your queries are# textually different every time, the query cache may result in a# slowdown instead of a performance improvement.query_cache_size = 1024M# Only cache result sets that are smaller than this limit. This is to# protect the query cache of a very large result set overwriting all# other query results.query_cache_limit = 2M# Minimum word length to be indexed by the full text search index.# You might wish to decrease it if you need to search for shorter words.# Note that you need to rebuild your FULLTEXT index, after you have# modified this value.ft_min_word_len = 4# If your system supports the memlock() function call, you might want to# enable this option while running MySQL to keep it locked in memory and# to avoid potential swapping out in case of high memory pressure. Good# for performance.#memlock# Table type which is used by default when creating new tables, if not# specified differently during the CREATE TABLE statement.default-storage-engine = innodb# Thread stack size to use. This amount of memory is always reserved at# connection time. MySQL itself usually needs no more than 64K of# memory, while if you use your own stack hungry UDF functions or your# OS requires more stack for some operations, you might need to set this# to a higher value.thread_stack = 192K# Set the default transaction isolation level. Levels available are:# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLEtransaction_isolation = REPEATABLE-READ# Maximum size for internal (in-memory) temporary tables. If a table# grows larger than this value, it is automatically converted to disk# based table This limitation is for a single table. There can be many# of them.tmp_table_size = 512M# Enable binary logging. This is required for acting as a MASTER in a# replication configuration. You also need the binary log if you need# the ability to do point in time recovery from your latest backup.log-bin=/data/mysql/binlog/binlog# binary logging format - mixed recommendedbinlog_format=ROW?

5.MySQL的管理脚本mysql.sh，内容如下，放在/usr/local/bin目录下，chmod +x /usr/local/bin/mysql.sh
mysql_port=3306mysql_username="root"mysql_password="yourpassword"function_start_mysql(){    printf "Starting MySQL...\n"    /bin/sh /usr/local/Percona-Server-5.5.21-rel25.0-227.Linux.x86_64/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/data/mysql/data --basedir=/usr/local/Percona-Server-5.5.21-rel25.0-227.Linux.x86_64/ 2>&1 > /dev/null &}function_stop_mysql(){    printf "Stoping MySQL...\n"    /usr/local/Percona-Server-5.5.21-rel25.0-227.Linux.x86_64/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -h localhost -P ${mysql_port} shutdown}function_restart_mysql(){    printf "Restarting MySQL...\n"    function_stop_mysql    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_mysqlelif [ "$1" = "stop" ]; then    function_stop_mysqlelif [ "$1" = "restart" ]; then    function_restart_mysqlelif [ "$1" = "kill" ]; then    function_kill_mysqlelse    printf "Usage: mysql.sh {start|stop|restart|kill}\n"fi
?
6.如果需要把mysqld设为系统服务并开机启动，可以使用以下方法：
vi /etc/init.d/mysqld
#!/bin/bash## comments to support chkconfig on RedHat Linux# chkconfig: 2345 90 90# description: MySQL is a World Wide db server.# processname: mysqld# config: /etc/my.conf# pidfile: /var/run/mysqld.pidmysql_port=3306mysql_username="root"mysql_password="yourpassword"function_start_mysql(){    printf "Starting MySQL...\n"    /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/data/mysql/data --basedir=/usr/local/mysql 2>&1 > /dev/null &}function_stop_mysql(){    printf "Stoping MySQL...\n"    /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -h localhost -P ${mysql_port} shutdown}function_restart_mysql(){    printf "Restarting MySQL...\n"    function_stop_mysql    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}')}function_status_mysql(){    run=`ps -ef | grep 'mysqld' | grep ${mysql_port} | wc -l`    if [ "$run" -eq 1 ]; then        printf "MySQL ${mysql_port} is running \n"    else        printf "MySQL ${mysql_port} is not running \n"    fi}if [ "$1" = "start" ]; then    function_start_mysqlelif [ "$1" = "stop" ]; then    function_stop_mysqlelif [ "$1" = "restart" ]; then    function_restart_mysqlelif [ "$1" = "status" ]; then    function_status_mysqlelif [ "$1" = "kill" ]; then    function_kill_mysqlelse    printf "Usage: mysql.sh {start|stop|restart|status|kill}\n"fi
?
chmod a+x mysqld
chkconfig --add mysqld
chkconfig --level 2345 mysqld on
使用service mysqld start和service mysqld stop 即可开启和关闭mysql服务

7.设置环境变量
vi /etc/profile
export PATH=/usr/local/Percona-Server-5.5.21-rel25.0-227.Linux.x86_64/bin:$PATH
source /etc/profile

8.启动MySQL数据库，设置root密码
/usr/local/bin/mysql.sh start
mysql -u root
GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "yourpassword";

9.安装handlersocket插件，在MySQL命令行执行 INSTALL PLUGIN handlersocket SONAME 'handlersocket.so';
在/etc/my.cnf的[mysqld]节下面增加以下内容：
loose_handlersocket_port = 9998# the port number to bind to (for read requests)loose_handlersocket_port_wr = 9999# the port number to bind to (for write requests)loose_handlersocket_threads = 16# the number of worker threads (for read requests)loose_handlersocket_threads_wr = 1# the number of worker threads (for write requests)
?
重启MySQL，/usr/local/bin/mysql.sh restart

10.检查handlersocket是否OK，在MySQL命令行执行 show processlist; 查看输出内容
  多谢
 多谢



缓存使用redis，采用什么方式实现mysql数据到redis中数据的同步
mysql udf + 触发器？
使用这种方式实现，但是有的mysql percona版本报错‘can't open shared library 'xxx.so' errno:0...’
不知道为什么？请问楼主遇到这个问题了吗？楼主采用什么方式实现数据同步到缓存？ 多谢



缓存使用redis，采用什么方式实现mysql数据到redis中数据的同步
mysql udf + 触发器？
使用这种方式实现，但是有的mysql percona版本报错‘can't open shared library 'xxx.so' errno:0...’
不知道为什么？请问楼主遇到这个问题了吗？楼主采用什么方式实现数据同步到缓存？

现在项目中很简单没有使用udf，直接在编码中实现的 ，先写db再写cache，后续考虑异步队列方式，udf思路很好，研究学习一下