MYSQL备忘
2019-06-02 本文已影响0人
Aieru
中央气象台
2020-07-11 12:00
news
MySQL
$ aptitude install mysql-server
$ vi /etc/mysql/my.cnf
Tips
- 中文乱码
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
