MySQL脚本书写规范,mysql安装和备份脚本的书写

mysql安装很大一部分参考了田逸写的《互联网运营智慧》，感谢田老师写这么好的书。田逸老师的博客：http://sery.blog.51cto.com/

环境：centos5.5 i386 +mysql 5.1.55

tar -zxvf mysql-5.1.55.tar.gz

cd mysql-5.1.55

groupadd mysql

useradd mysql -g mysql

mysql安装在/usr/local/mysql目录下 数据放在/data/mysql_db 下 增加utf8，gbk支持 以线程模式运行 如果想以后用innodb 引擎 编译参数增加 --with-plugins=innobase

./configure --prefix=/usr/local/mysql --localstatedir=/data/mysql_db --with-extra-charsets=utf8,gb2312,gbk --with-pthread --enable-thread-safe-client

或者选择下面的编译方式，对mysql编译进行了优化，参考了简朝阳的编译方法

./configure --prefix=/usr/local/mysql --without-debug --enable-thread-safe-client --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --localstatedir=/data/mysql_db --with-extra-charsets=utf8,gb2312,gbk --with-pthread

make

make install

cd  /usr/local/mysql

chown -R mysql .

chgrp -R mysql .

初始化数据库

bin/mysql_install_db --user=mysql

chown -R root .

cp support-files/my-large.cnf /etc/my.cnf(到mysql编译目录执行)

echo /usr/local/mysql/lib/mysql/lib  >>/etc/ld.so.conf

ldconfig

/usr/local/mysql/bin/mysqld_safe --user=mysql &(启动mysql)

ps -ef|grep mysql

更改mysql root密码为mysql

/usr/local/mysql/bin/mysqladmin  -uroot password mysql

/usr/local/mysql/bin/mysql -uroot -p

添加mysql开机自启动

echo "/usr/local/mysql/bin/mysqld_safe --user=mysql &" >>/etc/rc.d/rc.local

停止方法：/usr/local/mysql/bin/mysqladmin -u root -p shutdown

mysql默认安装后不允许远程访问，更改如下

/usr/local/mysql/bin/mysql -uroot -p

use mysql;

select user,host from user;

delete from user where user="";

select user,host from user;

delete from user where user="root" and host="127.0.0.1";

select user,host from user;

update user set host="%" where user="root";

更改mysql root密码为mysql

update user set password=password("mysql") where user="root";

select user,host from user; 直到只有root， host为"%"一条记录为止。否则继续delete from user where……

flush privileges;

修改mysql某些默认选项：

[root@server mysql-5.1.55]# more /etc/my.cnf |sed -n '/^#/!p'

[client]

port            = 3306

socket          = /tmp/mysql.sock

default-character-set=utf8 (需要增加)

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

default-character-set=utf8 (需要增加)

init_connect='SET NAMES utf8'  (需要增加)

max_connections=1000          (需要增加)

ft_min_word_len=1               (需要增加)

thread_concurrency = 8

log-bin=mysql-bin

expire_logs_days= 7          (需要增加)

binlog_format=mixed

server-id       = 1

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

变更更改并查看

/usr/local/mysql/bin/mysqladmin  -uroot -p shutdown

/usr/local/mysql/bin/mysqld_safe --user=mysql &

/usr/local/mysql/bin/mysql -uroot -p

show variables like "character_set_%";

mysql> show variables like "character_set_%";

+--------------------------+----------------------------------------+

| Variable_name            | Value                                  |

+--------------------------+----------------------------------------+

| character_set_client     | utf8                                   |

| character_set_connection | utf8                                   |

| character_set_database   | utf8                                   |

| character_set_filesystem | binary                                 |

| character_set_results    | utf8                                   |

| character_set_server     | utf8                                   |

| character_set_system     | utf8                                   |

| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |

+--------------------------+----------------------------------------+

8 rows in set (0.00 sec)

2 完全备份脚本编写：

每天对每个数据库都进行完全备份,备份成单个文件。备份到/data/mysqlback/执行脚本的日期/下，名称为：数据库名称.sql

#!/bin/bash

#mysql_back.sh:backup mysql databases

riqi=$(date +'%Y%m%d')

mysql=/usr/local/mysql/bin/mysql

mysqldump=/usr/local/mysql/bin/mysqldump

backdir=/data/mysqlback

mkdir -p /data/mysqlback/$riqi

#show all database

all_db="$($mysql  -uroot -pmysql -Bse 'show databases'|grep -v information_schema)"

for db in $all_db

do

$mysqldump --opt -uroot -pmysql $db --default-character-set=utf8 >$backdir/$riqi/$db.sql

done

echo "backup finished"

增加计划任务,每天凌晨三点对数据库进行完全备份。

crontab -e

0 3 * * *  sh /data/myscripts/mysql_back.sh

结尾附上阿里巴巴dba简朝阳编译方法：

./configure --prefix=/usr/local/mysql \

--without-debug \

--without-bench \

--enable-thread-safe-client \

--enable-assembler \

--enable-profiling \

--with-mysqld-ldflags=-all-static \

--with-client-ldflags=-all-static \

--with-charset=latin1 \

--with-extra-charset=utf8,gbk \

--with-innodb \

--with-csv-storage-engine \

--with-federated-storage-engine \

--with-mysqld-user=mysql \

--without-embedded-server \

--with-server-suffix=-community \

--with-unix-socket-path=/usr/local/mysql/sock/mysql.sock

附上mysql授权命令：

grant all privileges on *.* to mysqlbackup@'192.168.1.2' identified by 'mysql2'; flush privileges;