MySQL数据库多实例与主从同步

2018-04-05 分类:MySQL 阅读(621) 评论(0)

徐亮伟, 江湖人称标杆徐。多年互联网运维工作经验,曾负责过大规模集群架构自动化运维管理工作。擅长Web集群架构与自动化运维,曾负责国内某大型电商运维工作。
个人博客"徐亮伟架构师之路"累计受益数万人。
笔者Q:552408925、572891887
架构师群:471443208

mysql5.7编译安装

#安装环境准备

useradd -M -s /sbin/nologin mysql
yum -y install gcc gcc-c++ ncurses ncurses-devel cmake

#mysql5.7编译参数
wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.16.tar.gz
tar xf mysql-boost-5.7.16.tar.gz
cd mysql-5.7.16/
cmake -DCMAKE_INSTALL_PREFIX=/soft/mysql-5.7.16 \
-DMYSQL_UNIX_ADDR=/soft/mysql-5.7.16/data/mysql.sock \
-DMYSQL_DATADIR=/soft/mysql-5.7.16/data \
-DSYSCONFDIR=/soft/mysql-5.7.16/conf \
-DWITH_MYISAM_STORAGE_ENGINE=0 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=0 \
-DWITH_READLINE=1 \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_BOOST=/soft/package/src/mysql-5.7.16/boost/boost_1_59_0
make
make install

#完成后基本优化
ln -s /soft/mysql-5.7.16 /soft/mysql
mkdir /soft/mysql/data
chown -R mysql.mysql /soft/mysql
\cp /soft/mysql5.7/support-files/my-default.cnf /etc/my.cnf
\cp /soft/package/src/mysql-5.7.16/support-files/mysql.server /etc/init.d/mysqld
/soft/package/src/mysql-5.7.16/sql/mysqld --initialize-insecure --user=mysql --basedir=/soft/mysql --datadir=/soft/mysql/data
echo >> "export PATH=/data/soft/mysql/bin:$PATH" /etc/profile
source /etc/profile
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig --level 345 mysqld on
/etc/init.d/mysqld start
修改配置文件/etc/my.cnf

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
打赏作者
很喜欢这篇文章,打赏犒劳下作者,以此激励作者创作更多! (点击"打赏按钮",文章界面会变灰,往上翻阅可看见微信二维码)

您的支持将鼓励我们继续创作!

[微信] 扫描二维码打赏

标签:

您可能也喜欢:

企业实战Shell-MySQL分库分表备份脚本

今天是2015年的最后一天,大家都开心的跨年,而我还在苦逼的呵呵--省略 项目联系笔者QQ:572891887   也可以加入架构师交流群:471443208   进行交流 [root@db02 scripts]# cat /server/scripts/Store_backup.sh   #脚本...

more

MySQL数据库的优化

笔者联系QQ:572891887   也可以加入架构师交流群:471443208 我们究竟应该如何对MySQL数据库进行优化?下面我就从MySQL对硬件的选择、MySQL的安装、my.cnf的优化、MySQL如何进行架构设计及数据切分等方面来说明这个问题。 服务器物理硬件的优化 在挑选硬件服务器时...

more

欢迎新朋友你的到来!
还没有人抢沙发呢~

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

切换登录

注册

    - 00:00 / 00:00