安装编译源码所需工具和库(重要)
yum install gcc gcc-c++ ncurses-devel perl cmake bison
新建用户组及用户
groupadd mysql
useradd -r -g mysql mysql
新建安装目录
mkdir -p /usr/local/mysql mkdir -p /usr/local/mysql/data
下载MySQL源码
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.26.tar.gz
解压缩命令
tar -zxvf mysql-5.6.26.tar.gz
使用cmake设置源码编译配置脚本
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DMYSQL_DATADIR=/usr/local/mysql/data -DMYSQL_TCP_PORT=3306 -DENABLE_DOWNLOADS=1
ps: -DWITH_FEDERATED_STORAGE_ENGINE=1 跨服务器查询会用到, 如果编译的时候未指定FEDERATED引擎, 后期通过INSTALL PLUGIN的方式安装会出现问题
SQL>install plugin federated soname 'ha_federated.so';
ERROR 1126 (HY000): Can't open shared library '/usr/local/mysql/lib/mysql/plugin/ha_federated.so' (errno: 2 undefined symbol: dynstr_append_mem)
这是MySQL的bug, 只能通过编译源码重新安装解决, 项目难免会涉及到跨库操作, 所以此处在编译时手工指定FEDERATED引擎
编译源码
make
安装
make install
修改安装目录及数据库目录用户组及所有者
cd /usr/local/mysql chown -R mysql:mysql . cd /usr/local/mysql/data chown -R mysql:mysql .
初始化MySQL数据库
/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data/
复制配置文件
cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
修改默认字符集, 在my.cnf 文件 [mysqld] 标签下添加
character-set-server=utf8
创建mysqld系统服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
启动mysqld服务(CentOS 6 的命令)
service mysqld start
添加mysql系统命令, 编辑/etc/profile , 追加以下内容
PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
export PATH
使配置立即生效
source /etc/profile
进入控制台
[root@localhost etc]# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 Server version: 5.6.26 Source distribution Copyright (c) 2000, 2015, 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>
设置root用户密码和远程访问权限
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update user set password=password('123456') where user='root'; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option; Query OK, 0 rows affected (0.00 sec)
finished
补充:
查看数据库是否支持FEDERATED引擎
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
解决方法:
编辑 my.cnf 文件追加
federated
仅此一条, 重启mysqld服务, 再看一下
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)