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某些默认选项：
# more /etc/my.cnf |sed -n '/^#/!p'
port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8 （需要增加）
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
quick
max_allowed_packet = 16M
no-auto-rehash
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
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- \
--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'> flush privileges;
页:
[1]