Mysql 5.5.19 源码安装-配置优化-主从复制-自动备份

Mysql 5.5.19 源码安装-配置优化-主从复制-自动备份

Mysql 安装配置

软件源代码包存放位置/usr/local/src

源码包编译安装位置(prefix) /usr/local/

脚本以及维护程序存放位置/home/baison/script

数据库文件位置：/data/mysql/

数据库日志位置：/data/log

数据库备份目录：/data/backup

1、卸载默认的mysql及apache的rpm包

#yum remove httpd mysql mysql-server php php-cli php-common php-devel php-gd -y 2、更新时间

service ntpd stop

ntpdate https://www.360docs.net/doc/d313785083.html,

service ntpd start

chkconfig ntpd on

3、安装数据库

安装之前先安装编译环境及需要的库文件

#yum install gcc gcc-c++ make ncurses-devel -y

cd /usr/local/src

wget https://www.360docs.net/doc/d313785083.html,/files/v2.8/cmake-2.8.4.tar.gz

#tar xvf cmake-2.8.5.tar.gz

# cd cmake-2.8.5

# ./configure

make

make install

cd /usr/local/src

# tar zxvf mysql-5.5.19.tar.gz

# cd mysql-5.5.19

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql

-DMYSQL_DATADIR=/data/mysql

-DWITH_MYISAM_STORAGE_ENGINE=1

-DWITH_INNOBASE_STORAGE_ENGINE=1

-DWITH_ARCHIVE_STORAGE_ENGINE=1

-DWITH_BLACKHOLE_STORAGE_ENGINE=1

-DENABLED_LOCAL_INFILE=1

-DDEFAULT_CHARSET=utf8

-DDEFAULT_COLLATION=utf8_general_ci

-DEXTRA_CHARSETS=all

# make

#make install

mysql 配置

#userdel -r mysql

#groupadd -r -g 3306 mysql

# useradd -u 3306 -g mysql -r -M -s /sbin/nologin mysql

#mkdir -p /data/mysql/

#mkdir -p /data/log

#chown -R mysql:mysql /data/

#cp support-files/https://www.360docs.net/doc/d313785083.html,f /etc/https://www.360docs.net/doc/d313785083.html,f

cp support-files/https://www.360docs.net/doc/d313785083.html,f /etc/https://www.360docs.net/doc/d313785083.html,f

#/usr/local/mysql/scripts/mysql_install_db –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql & //初始化数据库

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

#chmod +x /etc/init.d/mysqld

# /chkconfig –add mysqld

#chkconfig mysqld on

# service mysqld start

# netstat -tnlp |grep 3306

# cd /usr/local/bin //进入用户的默认搜索路径下建立mysql命令的软连接，可以直接执行mysql命令

# ln -s /usr/local/mysql/bin/mysql mysql

# ln -s /usr/local/mysql/bin/mysqldump mysqldump

# ln -s /usr/local/mysql/bin/mysqladmin mysqladmin

# ln -s /usr/local/mysql/bin/mysqlbinlog

# mysqladmin -u root -p password ’123456′//设置数据库密码,可以不设置密码

# mysql -u root -p

mysql> flush privileges;

sudo vim /etc/https://www.360docs.net/doc/d313785083.html,f //Mysql 优化，在[mysqld]配置参数下面添加下面几行(大概37行下面),根据服务

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 500M

table_open_cache = 2048

sort_buffer_size = 8M

read_buffer_size = 4M

read_rnd_buffer_size = 16M

myisam_sort_buffer_size = 128M

###添加

log-error=/data/log/mysql.err.log

skip-name-resolv

max_connections = 1500

max_connect_errors = 10

slow_query_log

long_query_time = 2

slow_query_log_file = /data/log/slow.log

bulk_insert_buffer_size = 64M

query_cache_type = 1

query_cache_size =256M

query_cache_limit = 4M

ft_min_word_len = 2

join_buffer_size = 16M

innodb_file_per_table

log-bin-trust-function-creators=1

myisam_repair_threads = 1

innodb_log_files_in_group = 3

thread_cache_size = 8

# Try number of CPU’s*2 for thread_concurrency

thread_concurrency = 8

:wq //保存退出

sudo rm -rvf /data/mysql/data/ib_logfile* //删除原来的默认文件

sudo /sbin/service mysqld restart

Mysql> grant all privileges on *.* to openshop@’%’ identified by ‘openshop’; Mysql>flush privileges;

Mysql主从复制配置：

一、编辑主服务器的配置文件/etc/https://www.360docs.net/doc/d313785083.html,f，在[mysqld]中添加如下内容即可：

log-bin=mysql-bin

# binary logging format – mixed recommended

binlog_format=mixed //主从复制模式,混合模式（MBR）默认的

binlog_cache_size=4M //设置binlog缓存大小

max_binlog_size=300M //设置binlog文件最大体积

2、在master节点node1上建立复制用户，登入数据库进行用户授权

mysql>GRAN T REPLICATION SLAVE ON *.* TO ‘repl’@'%’ IDENTIFIED BY ‘repl’;

3、备份Master数据

备份Master上的数据，首先执行锁表操作，如下SQL语句：

mysql>FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.00 sec)

mysql> reset master;

Query OK, 0 rows affected (0.00 sec)

不要退出这个终端，否则这个锁就失效了；在不退出终端的情况，再开一个终端直接打包压缩数据文件或使用mysqldump工具来导出数据。

#mysqldump -uroot -h 127.0.0.1 –opt openshop >/data/mysql/backup/openshop.sql

#cd /data/mysql/backup/

4、备份数据库到从数据库上面

#tar -czvf openshop.tar.gz openshop.sql

#scp openshop.tar.gz root@10.7.70.42:/data/mysql/backup

导入数据库到其他Slave机器上。导入完成之后，在上面的命令终端上执行：

mysql>UNLOCK TABLES;

mysql> show processlist;

mysql> show master statusG；查看主服务器的bin-log 和id

二、设置Slave主机

1、编辑从数据库的vim /etc/https://www.360docs.net/doc/d313785083.html,f 配置文件，前面配置参数和主的一样，都是根据硬件内存调整参数

#binlog_format=mixed

# required unique id between 1 and 2^32 – 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id= 2

read_only=1

在slave上执行如下命令

mysql> CHANGE MASTER TO MASTER_HOST=’10.7.70.41′,

-> MASTER_USER=’repl’,

-> MASTER_PASSWORD=’repl’,

-> MASTER_LOG_FILE=’mysql-bin.000001′, //这里根据主服务器的binlog和id设置

-> MASTER_LOG_POS=107;

执行完之后执行：

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave statusG；

从输出看到：Slave_IO_Running和Slave_SQL_Running如果都为Yes时，表示配置成功

Mysql本地自动备份脚本

1、efast自动备份脚本：

vim /root/dbbackup.sh

#!/bin/bash

DATE=`date +%Y%m%d%H%M`

DATABASE=openshop

BACKUP=/data/backup

/usr/local/mysql/bin/mysqldump -uroot -h 127.0.0.1 -R –opt $DATABASE |gzip > ${BACKUP}/${DATABASE}_${DATE}.sql.gz

sleep 3

find $BACKUP -mtime +20 |xargs rm -rf

3自动清除binlog，保存10个binlog文件

vim /root/delbinlog.sh

#/bin/bash

LOGPUG=`mysql -e “show master logs”|awk ‘{print $1}’|grep -v “Log_name”|tail -10|head -1` mysql -e “PURGE BINARY LOGS TO ”$LOGPUG

4每天凌晨执行备份脚本：

Openshop数据库在每天3点整备份，e3数据库在每天3：30备份

[root@Xen1db01 script]# crontab -e

30 1 * * * /root/dbbackup.sh

30 3 * * 7 /root/delbinlog.sh