Percona Server 安装

安装配置参数

[root@monser Percona-Server-5.5.34-rel32.0]# cmake . -DCMAKE_INSTALL_PREFIX=/service/mysql -DMYSQL_DATADIR=/data/mysql -DSYSCONFDIR=/service/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_EXAMPLE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_TCP_PORT=30306 -DCOMPILATION_COMMENT=ZWC -DWITH_SSL=system -DWITH_DEBUG=0

[root@monser Percona-Server-5.5.34-rel32.0]# make && make install

创建配置文件

cat > /service/mysql/my.cnf << EOF
[client]
port = 30306
socket = /data/mysql/mysql.sock

[mysqld]
port = 30306
socket = /data/mysql/mysql.sock

basedir = /service/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
server-id = 1

skip-name-resolve
back_log = 600

max_connections = 5000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128 
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M

read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M

thread_cache_size = 8

query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4

log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 7

log_error = /data/mysql/mysql-error.log

slow_query_log = 1
long_query_time = 2
slow_query_log_file = /data/mysql/slowsql.log

performance_schema = 0
lower_case_table_names = 1
skip-external-locking

default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
federated  
event_scheduler = 1 
interactive_timeout = 28800
wait_timeout = 28800

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash 

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
EOF

初始化系统表，启动Percona Server

[root@monser Percona-Server-5.5.34-rel32.0]# /service/mysql/scripts/mysql_install_db --defaults-file=/service/mysql/my.cnf --basedir=/service/mysql --datadir=/data/mysql --user=mysql 
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:

/service/mysql/bin/mysqladmin -u root password 'new-password'
/service/mysql/bin/mysqladmin -u root -h 192.168.1.2 password 'new-password'

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

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

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

Percona recommends that all production deployments be protected with a support
contract (http://www.percona.com/mysql-suppport/) to ensure the highest uptime,
be eligible for hot fixes, and boost your team's productivity.
[root@monser Percona-Server-5.5.34-rel32.0]# /service/mysql/bin/mysqld_safe --defaults-file=/service/mysql/my.cnf --basedir=/service/mysql --datadir=/data/mysql --user=mysql & 
[1] 13051
[root@monser Percona-Server-5.5.34-rel32.0]# 140209 00:09:50 mysqld_safe Logging to '/data/mysql/mysql-error.log'.
140209 00:09:50 mysqld_safe Starting mysqld daemon with databases from /data/mysql

删除空用户

[root@monser mysql]# /service/mysql/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.34-log ZWC

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
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> grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'root' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'localhost' identified by 'root' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.user where Password='';
Query OK, 4 rows affected (0.00 sec)

mysql> delete from mysql.db where User='';
Query OK, 2 rows affected (0.00 sec)

mysql> delete from mysql.proxies_priv where Host!='localhost';
Query OK, 1 row affected (0.00 sec)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| FEDERATED          | YES     | Federated MySQL storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> exit
Bye
[root@monser mysql]# /service/mysql/bin/mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@monser mysql]# /service/mysql/bin/mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@monser mysql]# /service/mysql/bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.34-log ZWC

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
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.