mysql单机多实例配置笔记

  
  
[root@localhost ~]# cat /proc/version
Linux version 2.6.32-431.20.3.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-4) (GCC) ) #1 SMP Thu Jun 19 21:14:45 UTC 2014

查看Linux 的版本,CentOS，emm，okay.

   
   
[root@localhost ~]# rpm -qa | grep -i mysql
mysql-libs-5.1.73-3.el6_5.x86_64
[root@localhost ~]# rpm -e --nodeps mysql-libs-5.1.73-3.el6_5.x86_64
[root@localhost ~]# rpm -qa | grep mysql
[root@localhost ~]#

1.查看系统上有没有安装旧版本的mysql,一看发现有mysql5.1的.

3.删除之前安装的mysql的老的版本的

4.再查看还没有mysql,发现5已经没有了。

   
   
yum install  libevent*  libtool* autoconf* libstd* ncurse* bison* openssl*
yum -y install gcc gcc-c++

安装编译MySQL需要的依赖包。

   
   
wget http://www.cmake.org/files/v2.8/cmake-2.8.12.1.tar.gz
tar -xf cmake-2.8.12.1.tar.gz
cd cmake-2.8.12.1
./configure && make && make install

安装cmake，mysql5.5之后需要用cmake支持编译安装。都是rpm管理包，所以很流畅。

   
   
[root@localhost cmake-2.8.12.1]# cmake --version
cmake version 2.8.12.1

cmake 安装好之后查看cmake的版本号。

   
   
[root@localhost xyf]# wget http://mysql.com/Downloads/MySQL-5.6/mysql-5.6.14.tar.gz
--2014-07-18 14:14:14--  http://mysql.com/Downloads/MySQL-5.6/mysql-5.6.14.tar.gz
Resolving mysql.com... 137.254.60.6
Connecting to mysql.com|137.254.60.6|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: http://dev.mysql.com/Downloads/MySQL-5.6/mysql-5.6.14.tar.gz [following]
--2014-07-18 14:14:15--  http://dev.mysql.com/Downloads/MySQL-5.6/mysql-5.6.14.tar.gz
Resolving dev.mysql.com... 137.254.60.11
Connecting to dev.mysql.com|137.254.60.11|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.14.tar.gz [following]
--2014-07-18 14:14:16--  http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.14.tar.gz
Reusing existing connection to dev.mysql.com:80.
HTTP request sent, awaiting response... 302 Found
Location: http://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.14.tar.gz [following]
--2014-07-18 14:14:17--  http://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.14.tar.gz
Resolving cdn.mysql.com... 118.215.67.68
Connecting to cdn.mysql.com|118.215.67.68|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 36005278 (34M) [application/x-tar-gz]
Saving to: “mysql-5.6.14.tar.gz”
100%[======================================>] 36,005,278  11.3M/s   in 3.0s
2014-07-18 14:14:20 (11.3 MB/s) - “mysql-5.6.14.tar.gz” saved [36005278/36005278]

下载mysql。

接下来是编译安装mysql:

   
   
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DSYSCONFDIR=/etc \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
make && make install

第四步，配置mysql

添加mysql 用户组和用户

?

1 2	groupadd mysql useradd -g mysql mysql

赋予权限

?

1	chown -R mysql:mysql /usr/local/mysql

初始化配置

?

1 2	cd /usr/local/mysql scripts /mysql_install_db --basedir= /usr/local/mysql --datadir= /usr/local/mysql/data --user=mysql

配置自启动和启动mysql 

?

1 2 3

cp support-files /mysql .server /etc/init .d /mysql chkconfig mysql on service mysql start

配置PATH

?

1 2 3 4 5 6

vi /etc/profile #在文件末尾添加 PATH= /usr/local/mysql/bin :$PATH export PATH #保存退出，生效配置 source /etc/profile

登陆mysql 并设置root密码

?

1 2	mysql -uroot mysql> SET PASSWORD = PASSWORD( '123456' );

设置mysql可以远程访问

?

1	mysql> GRANT ALL PRIVILEGES ON *.* TO 'root' @ '%' IDENTIFIED BY '123456' WITH GRANT OPTION;

配置防火墙，允许3306端口

?

1 2 3 4 5

vi /etc/sysconfig/iptables #添加下面的一句 -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT #保存退出，重启防火墙 service iptables restart

这样，我们就能用windows 下的mysql IDE 远程管理mysql ，我用的Navicat for mysql。 

来源： <http://my.oschina.net/superbigfu/blog/176814>

 

---

npm方式安装mysql

cd /usr/local/src

wget http: //cdn .mysql.com /Downloads/MySQL-5 .6 /MySQL-server-5 .6.13-1.el6.x86_64.rpm

wget http: //cdn .mysql.com /Downloads/MySQL-5 .6 /MySQL-client-5 .6.13-1.el6.x86_64.rpm

 

#安装之前应该先查询系统是否自在了mysql的软件包 rpm -qa|grep -i mysql

#如果有的话需要先删除 rpm -e 软件名 --nodeps

 

rpm -ivh MySQL-server-5.6.13-1.el6.x86_64.rpm

rpm -ivh MySQL-client-5.6.13-1.el6.x86_64.rpm

 

 

#安装后生成的一个随机密码在 /root/.mysql_secret

#启动 MySQL

service mysql start

 

#登录mysql

mysql -u root -p

 

#修改密码

SET PASSWORD=password( 'password' );

 

#添加一个可远程登录的帐号

GRANT all privileges on *.* TO 'root' @ '%' identified by 'password' WITH GRANT OPTION;

FLUSH PRIVILEGES;

#修改密码

SET PASSWORD FOR 'root' @ '%' = PASSWORD( 'password' );

 

 

#########################################

 

#可运行此脚本 进行生产服务器安全配置

/usr/bin/mysql_secure_installation

 

 

#Enter current password for root  输入root密码

#Change the root password?  是否修改root的密码

#Remove anonymous users?  是否删除匿名账号

#Disallow root login remotely?  是否取消root用户远程登录

#Remove test database and access to it?  是否删除test库和对test库的访问权限

#Reload privilege tables now?  是否现在刷新授权表使修改生效

来源： <http://my.oschina.net/qianglong/blog/130860>

 

初始密码:CnEuvWfm

修改后的:

---

接下来:mysql5.6 单机多实例的配置

           MySQL具有一次运行多个实例的功能，实现一台服务器运行在不同端口使用不同数据文件的的mysql。他们是相互独立的。每个实例的datadir,pid,port,socket是不同的。

         

---

   
   
cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql -DSYSCONFDIR=/opt/mysql/etc -DMYSQL_DATADIR=/opt/mysql/data -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_USER=mysql -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1

   
   
cmake -DCMAKE_INSTALL_PREFIX=/data/mysql -DSYSCONFDIR=/data/mysql/etc -DMYSQL_DATADIR=/data/mysql/data -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_USER=mysql -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1

---

   
   
netstat -ntulp | grep mysql

   
   
/opt/mysql/init.d/mysqld_multi.server stop  3306,3307,3308,3309

启动mysql实例服务。

   
   
mysqld_multi --defaults-extra-file=/opt/mysql/etc/my.cnf start 1

   
   
[root@localhost ~]# netstat -ntulp | grep mysql
tcp        0      0 :::3307                     :::*                        LISTEN      16045/mysqld
tcp        0      0 :::3308                     :::*                        LISTEN      16772/mysqld
tcp        0      0 :::3309                     :::*                        LISTEN      17499/mysqld
tcp        0      0 :::3306                     :::*                        LISTEN      15314/mysqld

配置密码，权限

   
   
grant all privileges on *.* to 'root'@'localhost' identified by '!@#'

    
    
 grant all privileges on *.* to 'root'@'192.168.120.75' identified by '!@#02';

---

遇到的问题:

http://bbs.chinaunix.net/thread-500137-1-1.html

http://blog.fity.cn/post/349/

http://freeloda.blog.51cto.com/2033581/1349312