标签 mysql 下的文章

[分享]:mysql根据内存自动优化脚本

这个脚本可以根据服务器内存大小来修改/etc/my.cnf配置文件
如果要使用,请根据你自己配置文件情况修改后运行

Memtatol=`free -m | grep 'Mem:' | awk '{print $2}'`
if [ $Memtatol -gt 1500 -a $Memtatol -le 2500 ]; then
sed -i 's/table_open_cache = 128/table_open_cache = 256/g' /etc/my.cnf
sed -i 's/tmp_table_size = 16M/tmp_table_size = 32M/g' /etc/my.cnf
sed -i 's/thread_cache_size = 8/thread_cache_size = 16/g' /etc/my.cnf
sed -i 's/query_cache_size = 8M/query_cache_size = 16M/g' /etc/my.cnf
sed -i 's/innodb_buffer_pool_size = 64M/innodb_buffer_pool_size = 128M/g' /etc/my.cnf
sed -i 's/myisam_sort_buffer_size = 8M/myisam_sort_buffer_size = 16M/g' /etc/my.cnf
sed -i 's/key_buffer_size = 8M/key_buffer_size = 16M/g' /etc/my.cnf
elif [ $Memtatol -gt 2500 -a $Memtatol -le 3500 ]; then
sed -i 's/table_open_cache = 128/table_open_cache = 512/g' /etc/my.cnf
sed -i 's/tmp_table_size = 16M/tmp_table_size = 64M/g' /etc/my.cnf
sed -i 's/thread_cache_size = 8/thread_cache_size = 32/g' /etc/my.cnf
sed -i 's/query_cache_size = 8M/query_cache_size = 32M/g' /etc/my.cnf
sed -i 's/innodb_buffer_pool_size = 64M/innodb_buffer_pool_size = 512M/g' /etc/my.cnf
sed -i 's/myisam_sort_buffer_size = 8M/myisam_sort_buffer_size = 32M/g' /etc/my.cnf
sed -i 's/key_buffer_size = 8M/key_buffer_size = 64M/g' /etc/my.cnf
elif [ $Memtatol -gt 3500 ];then
sed -i 's/table_open_cache = 128/table_open_cache = 1024/g' /etc/my.cnf
sed -i 's/tmp_table_size = 16M/tmp_table_size = 128M/g' /etc/my.cnf
sed -i 's/thread_cache_size = 8/thread_cache_size = 64/g' /etc/my.cnf
sed -i 's/query_cache_size = 8M/query_cache_size = 64M/g' /etc/my.cnf
sed -i 's/innodb_buffer_pool_size = 64M/innodb_buffer_pool_size = 1024M/g' /etc/my.cnf
sed -i 's/myisam_sort_buffer_size = 8M/myisam_sort_buffer_size = 64M/g' /etc/my.cnf
sed -i 's/key_buffer_size = 8M/key_buffer_size = 256M/g' /etc/my.cnf
fi

Dockerfile:集成ssh和mysql

这个Dockerfile可以自动build集成ssh和mysql的image,并且可以通过修改my.cnf配置文件来自定义mysql数据库目录,同时可以指定ssh密码.
一共需要四个文件

1.Dockerfile:build镜像
2.run.sh :设置ssh,并且可以添加需要自启动的一些服务
3.set_root_pw.sh:启动容器时自定义ssh密码
4.my.cnf :mysql配置文件,可以修改相关参数

注:我在github中也提交了,地址是:https://github.com/insoz/docker-mysql

Dockerfile

FROM centos:centos6
MAINTAINER Fengnian Liu 

RUN yum -y install openssh-server epel-release && \
    yum -y install pwgen && \
    rm -f /etc/ssh/ssh_host_ecdsa_key /etc/ssh/ssh_host_rsa_key && \
    ssh-keygen -q -N "" -t dsa -f /etc/ssh/ssh_host_ecdsa_key && \
    ssh-keygen -q -N "" -t rsa -f /etc/ssh/ssh_host_rsa_key && \
    sed -i "s/#UsePrivilegeSeparation.*/UsePrivilegeSeparation no/g" /etc/ssh/sshd_config && \
    sed -i "s/UsePAM.*/UsePAM yes/g" /etc/ssh/sshd_config &&\
    yum -y install mysql mysql-devel mysql-server

VOLUME ["/data/docker/mysql","/data/mysql"]
RUN rm -rf /etc/my.cnf
ADD my.cnf /etc/my.cnf
ADD set_root_pw.sh /set_root_pw.sh
ADD run.sh /run.sh
RUN chmod +x /*.sh
RUN yum clean all

ENV AUTHORIZED_KEYS **None**

EXPOSE 22 3306
CMD ["/run.sh"]

run.sh

#!/bin/bash

if [ "${AUTHORIZED_KEYS}" != "**None**" ]; then
    echo "=> Found authorized keys"
    mkdir -p /root/.ssh
    chmod 700 /root/.ssh
    touch /root/.ssh/authorized_keys
    chmod 600 /root/.ssh/authorized_keys
    IFS=$'\n'
    arr=$(echo ${AUTHORIZED_KEYS} | tr "," "\n")
    for x in $arr
    do
        x=$(echo $x |sed -e 's/^ *//' -e 's/ *$//')
        cat /root/.ssh/authorized_keys | grep "$x" >/dev/null 2>&1
        if [ $? -ne 0 ]; then
            echo "=> Adding public key to /root/.ssh/authorized_keys: $x"
            echo "$x" >> /root/.ssh/authorized_keys
        fi
    done
fi

if [ ! -f /.root_pw_set ]; then
        /set_root_pw.sh
fi
service mysqld start
/usr/bin/mysqladmin -u root password 'insoz'
sed -i "/mysqladmin/d" /run.sh
sed -i "/run/d" /run.sh
exec /usr/sbin/sshd -D

set_root_pw.sh

#!/bin/bash

if [ -f /.root_pw_set ]; then
        echo "Root password already set!"
        exit 0
fi

PASS=${ROOT_PASS:-$(pwgen -s 12 1)}
_word=$( [ ${ROOT_PASS} ] && echo "preset" || echo "random" )
echo "=> Setting a ${_word} password to the root user"
echo "root:$PASS" | chpasswd

echo "=> Done!"
touch /.root_pw_set

echo "========================================================================"
echo "You can now connect to this CentOS container via SSH using:"
echo ""
echo "    ssh -p  root@"
echo "and enter the root password '$PASS' when prompted"
echo ""
echo "Please remember to change the above password as soon as possible!"
echo "========================================================================"

my.cnf

[client]
port		= 3306
socket		= /tmp/mysql.sock
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
datadir = /data/mysql
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 20M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query_cache_size = 32M
max_connections=500
wait_timeout=10
max_connect_errors = 100
wait_timeout=2880000
interactive_timeout = 2880000
log-bin=mysql-bin
binlog_format=mixed
server-id	= 1
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:500M;ibdata2:500M:autoextend
innodb_log_group_home_dir = /data/mysql
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

使用说明

docker build

[root@insoz docker-mysql]# docker build -t insoz/mysql:yum .

启动容器

docker run --restart always -d -p 127.0.0.1:22:22 -p 3306:3306 -v /data/docker/mysql:/data/mysql --name DB insoz/mysql:yum

设置数据库文件目录

-v /data/docker/mysql:/data/mysql

设置mysql密码
编辑 run.sh

/usr/bin/mysqladmin -u root password 'insoz'

设置ssh的root密码
启动容器时做如下操作

 docker run -d -p 0.0.0.0:2222:22 -e ROOT_PASS="mypass" insoz/mysql:yum

进入容器

ssh -p 22 root@127.0.0.1
docker exec -it 容器ID /bin/bash

MySQL主从服务器配置

主服务器 IP:192.168.1.138

从服务器IP:192.168.1.137

1、打开主服务器的mysql 配置文件

2、找到[mysqld]节点,添加或修改成以下内容。

server-id=1 #服务器ID
log-bin=mysql-bin01
binlog-do-db=leonis #这里设置需要在主服务器记录日志的数据库,只有在这里设置了的数据库才能被复制到从服务器
binlog-ignore-db=mysql #这里设置在主服务器上不记度日志的数据库
expire_logs_days=10

3、进入主服务器的mysql控制台,执行 FLUSH TABLES WITH READ LOCK;锁定现有数据库,再执行show master status;记录File和Position项目的值.

952334

4、执行 SHOW VARIABLES LIKE “%log_bin%”;  查看主服务器的binlog是否开启。 log_bin 这项 为 ON 的话就表示已开启。

936136

 

然后把数据库的锁定打开 UNLOCK TABLES;

5、在主服务器上创建从服务器使用的帐号并给予相应的权限(主要是replication slave权限),为避免配置过程中出现问题,可以也给予 reload,super权限,配好后再跟据实际情况取消。

grant replication slave, reload, super on *.* to ‘backup’@’192.168.0.1’ identified by ‘123456’; #backup是用户名,123456是密码

6、至此主服务器已设置完成。

7、接下来打开从服务器的mysql配置文件。在[mysqld]节点下,添加或修改成。

server-id=2
log-bin=mysql-bin02
replicate-do-db=leonis
replicate-ignore-db=mysql
expire_logs_days=10

#下面的配置只在mysql 5.1.7 之前的版本才有效。

master-host=192.168.1.138
master-user=root
master-password=leonis
master-port=3306

8、如果你是mysql 5.1.7之后版本的话,要进入从服务器的mysql控制台,使用命令设置。

change master to
master_host=‘192.168.1.138′,
master_user=’root’,
master_password=‘leonis’,
master_log_file=’mysql-bin01.000008′,  #此处填写主服务器的日志文件名,文章上方主服务状态信息中的File的值,上面已用红色强调。
master_log_pos=197; #此处填写主服务器日志文件记录的位置,文章上方主服务状态信息中的Position的值,上面已用红色强调。

9、执行上面命令后,再执行start slave,用启动从服务器模式。

10、可以使用 show processlist 查看进程,进程状态如和下方内容一样的话,说明从服务器运行正常,已经连接上主服务器,开始接受并执行日志。

127859

11、然后最好重启一下从服务器的MySQL。

12、在从服务器的MySQL控制台,使用show slave status; 命令,查看从服务器的运行状态。显示结果中以下两项都为Yes的话,那说明正常。

  1. Slave_IO_Running: Yes
  2. Slave_SQL_Running: Yes

13、在主服务器上leonis数据库创建表,写入数据,然后到从服务器上查看leonis数据库有没有进行同步。

Mysql 启动报错

1.查看mysql状态,报错如下:

MySQL is running but PID file could not be found

查询日志发现如下提示:

mysqld_safe mysqld from pid file /usr/local/mysql/data/centos.pid ended

解决方法:初始化mysql

/usr/local/mysql/bin/mysql_install_db –user=mysql

2.mysql无法启动,查看日志:

/usr/local/mysql/libexec/mysqld: Disk is full writing ‘./mysql-bin.~rec~’ (Errcode: 28). Waiting for someone to free space… (Expect up to 60 secs delay for server to continue after freeing disk space)

清理磁盘空间,重启OK!

Slave_SQL_Running: No mysql同步故障解决方法

Slave_SQL_Running: No mysql同步故障解决

     今天检查数据库发现一台MySQL Slave未和主机同步,查看Slave状态:
mysql> show slave statusG
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
….
Seconds_Behind_Master:NULL
原因:
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后,事务回滚造成的.
解决办法I:
1.首先停掉Slave服务:slave stop
2.到主服务器上查看主机状态:
记录File和Position对应的值。
mysql> show master status;
+——————+———–+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———–+————–+——————+
| mysql-bin.000020 | 135617781 | | |
+——————+———–+————–+——————+
1 row in set (0.00 sec)
3.到slave服务器上执行手动同步:
mysql> change master to
> master_host=’master_ip’,
> master_user=’user’,
> master_password=’pwd’,
> master_port=3307,
> master_log_file=’mysql-bin.000020′,
> master_log_pos=135617781;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)
再次查看slave状态发现:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Seconds_Behind_Master: 0
解决办法II:
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
自己的使用体会:方法一是强制性从某一个点开始同步,会有部分没有同步的数据丢失,后续主服务器上删除记录同步也会有一些错误信息,不会影响使用.方法二不一定会有效果.
=======================================================================================]
1,主从不能同步:
show slave status;报错:Error xxx dosn’t exist
且show slave statusG:
Slave_SQL_Running: NO
Seconds_Behind_Master: NULL
解决方法:
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;
之后Slave会和Master去同步 主要看:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master是否为0,0就是已经同步了
2,还需要做的一些优化与监视:
show full processlist; //查看mysql当前同步线程号
skip-name-resolve       //跳过dns名称查询,有助于加快连接及同步的速度
max_connections=1000    //增大Mysql的连接数目,(默认100)
max_connect_errors=100 //增大Mysql的错误连接数目,(默认10)
查看日志一些命令
1,  show master statusG;
在这里主要是看log-bin的文件是否相同。
show slave statusG;
在这里主要是看:
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
如果都是Yes,则说明配置成功.
2,在master上输入show processlistG;
mysql> SHOW PROCESSLISTG
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost:32931
db: NULL
Command: Binlog Dump
Time: 94
State: Has sent all binlog to slave; waiting for binlog to
be updated
Info: NULL
   如果出现Command: Binlog Dump,则说明配置成功.
stop slave    #停止同步
start slave    #开始同步,从日志终止的位置开始更新。
SET SQL_LOG_BIN=0|1  #主机端运行,需要super权限,用来开停日志,随意开停,会造成主机从机数据不一致,造成错误
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  # 客户端运行,用来跳过几个事件,只有当同步进程出现错误而停止的时候才可以执行。
RESET MASTER  #主机端运行,清除所有的日志,这条命令就是原来的FLUSH MASTER
RESET SLAVE   #从机运行,清除日志同步位置标志,并重新生成master.info
虽然重新生成了master.info,但是并不起用,最好,将从机的mysql进程重启一下,
LOAD TABLE tblname FROM MASTER #从机运行,从主机端重读指定的表的数据,每次只能读取一个,受timeout时间限制,需要调整timeout时间。执行这个命令需要同步账号有 reload和super权限。以及对相应的库有select权限。如果表比较大,要增加net_read_timeout 和 net_write_timeout的值
LOAD DATA FROM MASTER  #从机执行,从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大,要增加net_read_timeout 和 net_write_timeout的值
CHANGE MASTER TO master_def_list  #在线改变一些主机设置,多个用逗号间隔,比如
CHANGE MASTER TO
MASTER_HOST=’master2.mycompany.com’,
MASTER_USER=’replication’,
MASTER_PASSWORD=’bigs3cret’
MASTER_POS_WAIT() #从机运行
SHOW MASTER STATUS #主机运行,看日志导出信息
SHOW SLAVE HOSTS #主机运行,看连入的从机的情况。
SHOW SLAVE STATUS (slave)
SHOW MASTER LOGS (master)
SHOW BINLOG EVENTS [ IN ‘logname’ ] [ FROM pos ] [ LIMIT [offset,] rows ]
PURGE [MASTER] LOGS TO ‘logname’ ; PURGE [MASTER] LOGS BEFORE ‘date’

Mysql 重置密码

当忘记MySQL的root密码的时候,可以进行如下的步骤重新设置

首先确认服务器出于安全的状态,也就是没有人能够任意地连接MySQL数据库。因为在重新设置MySQL的root密码的期间,MySQL数据库完全出于没有密码保护的状态下,其他的用户也可以任意地登录和修改MySQL的信息。可以采用将MySQL对外的端口封闭,并且停止Apache以及所有的用户进程的方法实现服务器的准安全状态。最安全的状态是到服务器的Console上面操作,并且拔掉网线。

一、LINUX

1<修改MySQL的登录设置:
# vi /etc/my.cnf
在[mysqld]的段中加上一句:skip-grant-tables
例如:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
skip-grant-tables

保存并且退出vi。
2<重新启动mysqld

# /etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

3<登录并修改MySQL的root密码

# /usr/bin/mysql
.
mysql< USE mysql ;
mysql< UPDATE user SET Password = password(‘new-password’) WHERE User = ‘root’ ;
mysql< flush privileges ;
mysql< quit

4<将MySQL的登录设置修改回来

# vi /etc/my.cnf

将刚才在[mysqld]的段中加上的skip-grant-tables删除
保存并且退出vi。
5<重新启动mysqld

# /etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

二、WINDOWS

1<以系统管理员身份登陆系统。

2<打开cmd—–net start 查看mysql是否启动。启动的话就停止net stop mysql
.
3<我的mysql安装在d:usrlocalmysql4bin下。

4<跳过权限检查启动mysql.
d:usrlocalmysql4binmysqld-nt –skip-grant-tables

5<重新打开cmd。进到d:usrlocalmysql4bin下:
d:usrlocalmysql4binmysqladmin -uroot flush-privileges password “newpassword”
d:usrlocalmysql4binmysqladmin -u root -p shutdown 这句提示你重新输密码。
6<在cmd里net start mysql
7<搞定了。
附:MySQL4.1以上版本一种密码错误问题的解决方法
1 # SET PASSWORD FOR ‘some_user’@’some_host’ = OLD_PASSWORD(‘newpwd’);
2 # FLUSH PRIVILEGES;
3,Mysql数据库修复
myisamchk -r -q d:mysqldatalatin1*
r代表修复
q代表快速
d:mysqldatalatin1*数据库里面 *代表里面的所有的文件