MYSQL备忘

中央气象台

2020-07-11 12:00

news

MySQL

$ aptitude install mysql-server
$ vi /etc/mysql/my.cnf

Tips

1. 中文乱码
   mysql> set names 'utf8'

权限设置

1､ 改表法
$ mysql -u root -p
$ mysql> use mysql;
$ mysql> update user set host = '%' where user = 'root';
$ mysql> select host, user from user;

2、授权法
mysql -h localhost -u root
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
# GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
# GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.3' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES

修改密码

1. 使用mysqladmin， mysqladmin -u root -p password mypasswd 
2. mysql> REPLACE INTO mysql.user (Host,User,Password) 
                    VALUES ('%','username',PASSWORD('password')); 
   mysql> FLUSH PRIVILEGES 
   
3. mysql> SET PASSWORD FOR root@"%" = PASSWORD('password'); 
4. mysql> GRANT USAGE ON *.* TO root@"%" IDENTIFIED BY 'password'; 

Error 144 [database/table] is marked as crashed and last (automatic?) repair failed

myisamchk -r q  table_name # 快速修复模式,会自动检查索引表和数据表是否能够一致
myisamchk -r table_name   # 删除不一致的数据和索引，并重新构建索引
myisamchk --safe-recover table_name;

Dump & source

1）导出整个数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名 
mysqldump -u wcnc -p dbname > dbdump.sql

2）导出一个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u root -p dbname table > dbdump.sql

3）导出一个数据库结构

mysqldump -u root -p -d --add-drop-table dbname > dbdump.sql
#-d 不导出数据只导出结构 --add-drop-table 在每个create语句之前增加一个drop table 

4）导入数据库，常用source 命令

mysql -u root -p 
mysql>use dbname
mysql>set names utf8; （先确认编码，如果不设置可能会出现乱码，注意不是UTF-8） 
#然后使用source命令，后面参数为脚本文件（如这里用到的.sql）
mysql>source ~/dbdump.sql

上边的实例只是最基础的，有的时候我们可能需要批量导出多个库，我们就可以加上--databases 或者-B，如下语句：

mysqldump  -uroot -p --databases test mysql #空格分隔
还有的时候我们可能需要把数据库内所有的库全部备份，我们就可以使用-all-databases，如下语句：

mysqldump  -uroot -p -all-databases

HA 配置

edit my.cnf

[mysqld]
datadir                  = /data/mysql
# bind-address           = 127.0.0.1
server-id                = 11  # 每台服务器分配
log_bin                  = /var/log/mysql/mysql-bin.log
binlog_format            = mixed
binlog_do_db             = ewins
binlog_ignore_db         = mysql,information_schema
replicate_do_db          = ewins
replicate_ignore_db      = mysql,information_schema
slave-skip-errors        = all

# auto_increment_offset    = 1
# auto_increment_increment = 2
# sync_binlog             = 1
# log-slave-updates       = 1 

$ /etc/init.d/mysql restart

master 执行

$ mysql -uroot -p
mysql> GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.56.102' identified by 'password';
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 107      |              |                  |
+------------------+----------+--------------+------------------+

slave 执行

mysql> change master to
master_host='192.168.1.101',
master_user='root',
master_password='passwd',
master_log_file='mysql-bin.000002',
master_log_pos=427;

mysql> start slave;
mysql> show slave status\G

Slave_IO_Running 与 Slave_SQL_Running 都是 YES，才表明状态正常。

Keepalived Configuration file

! Configuration File for keepalived
global_defs {
    router_id MYSQL_HA
}

vrrp_script check_run {
    script "/data/keepalived/check_mysql.sh"
    interval 5
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 50
    priority 101
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass passwd
    }
    virtual_ipaddress {
        172.20.29.242
    }
    track_script {
        check_run
    }
}

check_mysql.sh

#!/bin/bash
MYSQL_USER=root
MYSQL_PASS=passwd
CHECK_COUNT=5

counter=1
while true
do
    mysql -u$MYSQL_USER -p$MYSQL_PASS -e "show status;"  >/dev/null 2>&1
    i=$?
    ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
    j=$?
    if [ $i = 0 ] && [ $j = 0 ]
    then
        exit 0
    else
        if [ $i = 1 ] && [ $j = 0 ]
        then
            exit 0
        else
            if [ $counter -gt $CHECK_COUNT ]
            then
                break
            fi
        let counter++
        continue
        fi
    fi
done
/etc/init.d/keepalived stop
exit 1

Sequence function

CREATE TABLE `SEQUENCE` (
  `NAME` varchar(255) NOT NULL,
  `CURRENTVALUE` int(11) unsigned DEFAULT NULL,
  `INCREMENT` int(2) DEFAULT NULL,
  PRIMARY KEY (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

set global log_bin_trust_function_creators=1;

delimiter //

CREATE DEFINER=`root`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS int(11)
BEGIN  
  DECLARE value INTEGER;  
  SET value = 0;  
  SELECT CURRENTVALUE INTO value FROM SEQUENCE  
  WHERE NAME = seq_name;  
  RETURN value;  
END//

CREATE DEFINER=`root`@`%` FUNCTION `setval`(seq_name VARCHAR(50), value INTEGER) RETURNS int(11)
BEGIN  
   UPDATE SEQUENCE  
   SET  CURRENTVALUE = value  
   WHERE NAME = seq_name;  
   RETURN currval(seq_name);  
END//

CREATE DEFINER=`root`@`%` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS int(11)
BEGIN  
   DECLARE exist int(1);
   SELECT count(1) into exist FROM SEQUENCE WHERE NAME = seq_name;
   if exist = 0 then
       INSERT INTO SEQUENCE (NAME, CURRENTVALUE, INCREMENT)
       VALUES ( seq_name, 1, 1);
       RETURN 1;
   else
       UPDATE SEQUENCE  
       SET  CURRENTVALUE = CURRENTVALUE + INCREMENT  
       WHERE NAME = seq_name;  
       RETURN currval(seq_name);  
   end if;
END//

delimiter ;

dump脚本

# !/bin/bash 
# This is a ShellScript For Auto DB Backup 
# Setting 
# 设置数据库名，数据库登录名，密码，备份路径，日志路径，数据文件位置，以及备份方式 
# 默认情况下备份方式是tar，还可以是mysqldump,mysqldotcopy 
# 默认情况下，用root(空)登录mysql数据库，备份至/root/dbxxxxx.tgz 

DBName=develop 
DBUser=root 
DBPasswd=root
BackupPath=/home/karas/ 
LogFile=/home/karas/db.log 
DBPath=/var/lib/mysql/ 
BackupMethod=mysqldump 

# BackupMethod=mysqlhotcopy 

# BackupMethod=tar 

# Setting End 

NewFile="$BackupPath"db$(date +%y%m%d).tgz 
DumpFile="$BackupPath"db$(date +%y%m%d) 
OldFile="$BackupPath"db$(date +%y%m%d --date='5 days ago').tgz 

echo "-------------------------------------------" >;>; $LogFile 
echo $(date +"%y-%m-%d %H:%M:%S") >;>; $LogFile 
echo "--------------------------" >;>; $LogFile 

# Delete Old File 

if [ -f $OldFile ] 
then 
   rm -f $OldFile >;>; $LogFile 2>;&1 
   echo "[$OldFile]Delete Old File Success!" >;>; $LogFile 
else 
   echo "[$OldFile]No Old Backup File!" >;>; $LogFile 
fi 

if [ -f $NewFile ] 
then 
   echo "[$NewFile]The Backup File is exists,Can't Backup!" >;>; $LogFile 
else 
   case $BackupMethod in 
   mysqldump) 
      if [ -z $DBPasswd ] 
      then 
         mysqldump -u $DBUser --opt $DBName >; $DumpFile 
      else 
         mysqldump -u $DBUser -p$DBPasswd --opt $DBName >; $DumpFile 
      fi 
      tar czvf $NewFile $DumpFile >;>; $LogFile 2>;&1 
      echo "[$NewFile]Backup Success!" >;>; $LogFile 
      rm -rf $DumpFile 
      ;; 
   mysqlhotcopy) 
      rm -rf $DumpFile 
      mkdir $DumpFile 
      if [ -z $DBPasswd ] 
      then 
         mysqlhotcopy -u $DBUser $DBName $DumpFile >;>; $LogFile 2>;&1 
      else 
         mysqlhotcopy -u $DBUser -p $DBPasswd $DBName $DumpFile >;>;$LogFile 2>;&1 
      fi 
      tar czvf $NewFile $DumpFile >;>; $LogFile 2>;&1 
      echo "[$NewFile]Backup Success!" >;>; $LogFile 
      rm -rf $DumpFile 
      ;; 
   *) 
      /etc/init.d/mysqld stop >;/dev/null 2>;&1 
      tar czvf $NewFile $DBPath$DBName >;>; $LogFile 2>;&1 
      /etc/init.d/mysqld start >;/dev/null 2>;&1 
      echo "[$NewFile]Backup Success!" >;>; $LogFile 
      ;; 
   esac 
fi 

echo "-------------------------------------------" >;>; $LogFile