mysql 5.5多实例_MySQL5.5安装与多实例

MySQL5.5编译安装

#1.2 cmake软件

cd /home/oldboy/tools/

tar xf cmake-2.8.8.tar.gz

cd cmake-2.8.8

./configure

#CMake has bootstrapped. Now run gmake.

gmake

gmake install

cd ../

#1.3依赖包

yum install ncurses-devel -y

#1.4创建用户和组

groupadd mysql

useradd mysql -s /sbin/nologin -M -g mysql

#1.5解压编译MySQL

tar zxf mysql-5.5.32.tar.gz

cd mysql-5.5.32

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \

-DMYSQL_DATADIR=/application/mysql-5.5.32/data \

-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

-DENABLED_LOCAL_INFILE=ON \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \

-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \

-DWITH_FAST_MUTEXES=1 \

-DWITH_ZLIB=bundled \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_READLINE=1 \

-DWITH_EMBEDDED_SERVER=1 \

-DWITH_DEBUG=0

#-- Build files have been written to: /home/oldboy/tools/mysql-5.5.32

提示，编译时可配置的选项很多，具体可参考结尾附录或官方文档：

make

#[100%] Built target my_safe_process

make install

ln -s /application/mysql-5.5.32/ /application/mysql

chown -R mysql.mysql /application/mysql/data

chmod -R 1777 /tmp

cp support-files/my-large.cnf /etc/my.cnf

cd scripts/

./mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql

/bin/cp support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

/etc/init.d/mysqld start

如果上述操作未出现错误，则MySQL5.5.32软件cmake方式的安装就算成功了。

MySQL5.5二进制安装

useradd mysql -s /sbin/nologin -M

mkdir /data/tools/ -p

wget http://download.xuliangwei.com/mysql-5.5.32-linux2.6-x86_64.tar.gz

tar xf mysql-5.5.32-linux2.6-x86_64.tar.gz

mv mysql-5.5.32-linux2.6-x86_64 /data/mysql-5.5.32

ln -s /data/mysql-5.5.32/ /data/mysql

chown -R mysql.mysql /data/mysql/

/data/mysql/scripts/mysql_install_db --basedir=/data/mysql/ --datadir=/data/3306/ --user=mysql

\cp /data/mysql/support-files/mysql.server /etc/init.d/mysqld

sed -i 's#/usr/local/mysql#/data/mysql#g' /data/mysql/bin/mysqld_safe /etc/init.d/mysqld

\cp /data/mysql/support-files/my-small.cnf /etc/my.cnf

echo 'export PATH=/data/mysql/bin:$PATH' >/etc/profile.d/mysqld.sh

sh /etc/profile.d/mysqld.sh

/etc/init.d/mysqld start

mysqladmin password 123456

mysql -uroot -p123456

mysql

#二进制安装详解

1.安装mysql

ls mysql-5.5.32-linux2.6-x86_64.tar.gz

tar xf mysql-5.5.32-linux2.6-x86_64.tar.gz

mv mysql-5.5.32-linux2.6-x86_64 /data/mysql-5.5.32

ln -s /data/mysql-5.5.32 /data/mysql

##########相当于编译步骤的make install完成#############

2.创建用户和目录

useradd mysql -s /sbin/nologin -M

chown -R mysql.mysql /data/mysql/data/

3.初始化数据库

cd /data/mysql

./scripts/mysql_install_db --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data/

\cp support-files/my-small.cnf /etc/my.cnf

cp support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

sed -i 's#/usr/local/mysql#/data/mysql#g' /data/mysql/bin/mysqld_safe /etc/init.d/mysqld

/etc/init.d/mysqld start

netstat -lntup|grep mysql

cp -a /data/mysql/bin/* /usr/local/sbin/

mysqladmin password oldboy123

mysql -uroot -poldboy123

##mysql安装报错

Installing MySQL system tables.../application/mysql//bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

解决方法： yum -y install libaio

/application/mysql//bin/mysqld: Can't create/write to file '/tmp/ibIgpX1g' (Errcode: 13 - Permission denied)

2015-09-28 02:53:35 7f5cb05e6720 InnoDB: Error: unable to create temporary file; errno: 13

2015-09-28 02:53:35 1170 [ERROR] Plugin 'InnoDB' init function returned error.

2015-09-28 02:53:35 1170 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2015-09-28 02:53:35 1170 [ERROR] Unknown/unsupported storage engine: InnoDB

2015-09-28 02:53:35 1170 [ERROR] Aborting

解决方法：chmod 1777 /tmp/

##mysql启动报错

Starting MySQL.The server quit without updating PID file (/[FAILED]ql/darker.pid).

InnoDB: mmap(137363456 bytes) failed; errno 12

[ERROR] InnoDB: Cannot allocate memory for the buffer pool

[ERROR] Plugin 'InnoDB' init function returned error.

[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

[ERROR] Unknown/unsupported storage engine: InnoDB

[ERROR] Aborting

解决：vim /etc/my.cnf innodb_buffer_pool_size = 8M 修改innodb为8M,内存过小导致无法启动

MySQL5.5多实例安装

1.安装单实例MySQL

useradd mysql -s /sbin/nologin -M

mkdir /application/tools/ -p

cd /application/tools/

tar xf mysql-5.5.32-linux2.6-x86_64.tar.gz

mv mysql-5.5.32-linux2.6-x86_64 /application/mysql-5.5.32

ln -s /application/mysql-5.5.32/ /application/mysql

chown -R mysql.mysql /application/mysql/

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql

\cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld

sed -i 's#/usr/local/mysql#/application/mysql#g' /application/mysql/bin/mysqld_safe /etc/init.d/mysqld

\cp /application/mysql/support-files/my-small.cnf /etc/my.cnf

echo 'export PATH=/application/mysql/bin:$PATH' >/etc/profile.d/mysqld.sh

2.配置多实例

mkdir /data/{3306,3307}/data -p 创建多实例目录

解压已经准备好的data包

[root@MySQL01 application]# find /data/ -type f -name "mysql"|xargs chmod +x

[root@MySQL01 application]# chown -R mysql.mysql /data/

[root@MySQL01 ~]# cd /application/mysql

[root@MySQL01 ~]#./scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3306/data --user=mysql

[root@MySQL01 ~]#./scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307/data --user=mysql

3.增加一个MySQL 3309端口实例过程如下：

mkdir -p /data/3308/data

\cp /data/3306/my.cnf /data/3308/

\cp /data/3306/mysql /data/3308/

sed -i 's/3306/3308/g' /data/3308/my.cnf

sed -i 's/3306/3308/g' /data/3308/mysql

sed -i 's#server-id = 1#server-id = 8#g’ /data/3308/my.cnf

chown -R mysql:mysql /data/3308

chmod 700 /data/3308/mysql

cd /application/mysql

./scripts/mysql_install_db --datadir=/data/3308/data --basedir=/application/mysql --user=mysql

chown -R mysql:mysql /data/3308

/data/3308/mysql start

netstat -lnt|grep 3308

如果没起来看mysql错误日志，在/data/3308/my.cnf最下面有错误日志地址

echo "#mysql multi instances" >>/etc/rc.local

echo "/data/3306/mysql start" >>/etc/rc.local

echo "/data/3307/mysql start" >>/etc/rc.local

4.增加一个MySQL 3309端口实例过程如下：

mkdir -p /data/3309/data

\cp /data/3306/my.cnf /data/3309/

\cp /data/3306/mysql /data/3309/

sed -i 's/3306/3309/g' /data/3309/my.cnf

sed -i 's/3306/3309/g' /data/3309/mysql

sed -i 's#server-id = 1#server-id = 9#g' /data/3309/my.cnf

chown -R mysql:mysql /data/3309

chmod 700 /data/3309/mysql

cd /application/mysql

./scripts/mysql_install_db --datadir=/data/3309/data --basedir=/application/mysql --user=mysql

chown -R mysql:mysql /data/3309

/data/3309/mysql start

netstat -lnt|grep 3309

如果没起来看mysql错误日志，在/data/3309/my.cnf最下面有错误日志地址

echo "#mysql multi instances" >>/etc/rc.local

echo "/data/3306/mysql start" >>/etc/rc.local

echo "/data/3307/mysql start" >>/etc/rc.local

echo "/data/3309/mysql start" >>/etc/rc.local

MySQL5.5多实例主从

1.备主库

mysqldump -A -uroot -p'123456' -S /data/3306/mysql.sock --master-data=2 >/opt/alldb20161122.sql

2.把整个备份文件恢复到从库

mysql -uroot -p123456 -S /data/3307/mysql.sock < /opt/alldb20161122.sql

3.过滤出binlog位置和master_log_pos位置点

grep -i 'change master' /opt/alldb20161122.sql |head -n 1

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=250;

4.主库授权

grant all on *.* to 'repl'@'%' identified by '123456';

5.配置从库

CHANGE MASTER TO MASTER_HOST=’10.8.2.78’, MASTER_USER='repl',MASTER_PASSWORD=‘123456’, MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=250;

slave start;

6.检查从库

show slave status\G