CentOS6源码安装MySQL

安装编译源码所需工具和库(重要)

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数据库

cd /usr/local/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] 标签下添加

vim /usr/local/mysql/my.cnf
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)