mysql percona版编译安装

安装依赖
# yum install gcc-c++ make cmake bison bison-devel ncurses-devel libaio-devel perl
对于 Fedora 20 和 CentOS 7 需要另外安装一个 perl-Data-Dumper 包：


# yum install  perl-Data-Dumper


创建 MySQL 用户
# groupadd mysql
# useradd -g mysql -s /sbin/nologin -M mysql




cmake安装
cd lnmp/src
tar xzf cmake-2.8.11.2.tar.gz
cd cmake-2.8.11.2
./configure
make &&  make install
cd ..




Percona 5.6预编译
tar zxf percona-server-5.6.23-72.1.tar.gz
cd percona-server-5.6.23-72.1
mkdir -p /data/percona
chown mysql.mysql -R /data/percona
mkdir /data/mysql
chown mysql.mysql -R  /data/mysql




cmake . -DCMAKE_INSTALL_PREFIX=/data/mysql \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DMYSQL_DATADIR=/data/percona \
-DSYSCONFDIR=/etc \
-DMYSQL_TCP_PORT=3306 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DMYSQL_USER=mysql \
-DWITH_EXTRA_CHARSETS=all








编译参数解释：
CMAKE_INSTALL_PREFIX #安装路径
MYSQL_UNIX_ADDR #unix socket路径
MYSQL_DATADIR #数据库存放路径
SYSCONFDIR #配置文件路径
MYSQL_TCP_PORT #数据库端口
WITH_INNOBASE_STORAGE_ENGINE #安装innodb引擎
WITH_PARTITION_STORAGE_ENGINE #安装分区引擎
WITH_BLACKHOLE_STORAGE_ENGINE #安装blackhole引擎
WITH_MYISAM_STORAGE_ENGINE #安装myisam引擎
WITH_READLINE #快捷键功能
ENABLED_LOCAL_INFILE #允许从本地导入数据
DEFAULT_CHARSET #默认编码
DEFAULT_COLLATION #默认字符集
WITH_EXTRA_CHARSETS #是否使用其他编码，all表示所有编译安装




编译安装
make && make install


启动脚本，设置开机自启动
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on


初始化mysql
cd ./script
./mysql_install_db --user=mysql  --basedir=/data/mysql --datadir=/data/percona


vi /etc/my.cof
[client]
#password   = [your_password]
port        = 3306
socket      = /tmp/mysql.sock
default-character-set=utf8
# *** Application-specific options follow here ***


[mysqld]
basedir = /data/mysql
datadir=/data/percona
socket=/tmp/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
skip-federated
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_general_ci
bind-address=0.0.0.0
lower_case_table_names=1
event_scheduler=1
[mysqld_safe]
log-error=/data/mysql/mysqld.log
pid-file=/data/percona/mysqld.pid
更改datadir和pid-file解决找不到pid文件的问题


重启mysql
service  mysqld restart






解决mysql无法登陆的问题
# /etc/init.d/mysql stop 
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking & 
# mysql -u root mysql 
mysql> update user set password=password('mysql') where user='root'; 
mysql> FLUSH PRIVILEGES; 
mysql> quit 
# /etc/init.d/mysql restart 
# mysql -uroot -p 
Enter password: <输入新设的密码newpassword> 
mysql> grant all privileges on *.* to root@'%' identified by "mysql" with grant option;








/usr/local/percona/bin/mysql -uroot -pmysql -e "grant all privileges on *.* to root@'127.0.0.1' identified by "mysql" with grant option;"
/usr/local/percona/bin/mysql -uroot -pmysql -e "grant all privileges on *.* to root@'localhost' identified by "mysql" with grant option;"
/usr/local/percona/bin/mysql -uroot -pmysql -e "delete from mysql.user where Password='';"
/usr/local/percona/bin/mysql -uroot -pmysql -e "delete from mysql.db where User='';"
/usr/local/percona/bin/mysql -uroot -pmysql -e "drop database test;"
/usr/local/percona/bin/mysql -uroot -pmysql -e "reset master;"




主创建同步用户
mysync'@'192.168.56.103‘ ip指定从mysql服务器ip，或者可以指定一个IP段192.168.56.%
GRANT REPLICATION SLAVE ON *.* to 'mysync'@'192.168.56.103' identified by 'q123456';


show  master status;


从
master_host指定到主mysql
change master to master_host='192.168.56.102',master_user='mysync',master_password='q123456',master_log_file='mysql-bin.000002',master_log_pos=333;
start slave;




Linux下开启端口
/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT #开启8011端口 
/etc/rc.d/init.d/iptables save #保存配置  
/etc/rc.d/init.d/iptables restart #重启服务 
 
查看端口是否已经开放 
/etc/init.d/iptables status