mysql与sqlite3数据库之间相互导入导出

1.mysql库导入到sqlite3中

##下载并上传mysql2sqilite压缩包至目标主机/etc/grafana目录下

https://github.com/dumblob/mysql2sqlite/archive/refs/heads/master.zip

##解压使用该工具

cd /etc/grafana
unzip mysql2sqlite-master.zip
cd mysql2sqlite-master

##使用mysqldump导出mysql的grafana库

mysqldump -uroot -proot grafana > grarana_`date "+%F"`.sql            
ll /etc/grafana/grafana0519.sql 

##转换mysql库文件格式为sqlite3的库文件格式

./mysql2sqlite grafana0519.sql | sqlite3 mysqlite3.db   

##加载刚转换格式的文件为sqlite3的库

sqlite3  mysqlite3.db  
sqlite> .databases
sqlite> .tables
sqlite> select * from user;

 

2.sqlite3库导入到mysql中

##找到并备份sqlite3数据库文件

cp /var/lib/grafana/grafana.db /root/sqlite66/66grafana.db

cd /root/sqlite66/

##使用sqlite3的.dump命令导出库文件

sqlite3 /root/sqlite66/66grafana.db .dump > /root/sqlite66/dump.sql

##使用python代码转换sqlite3语法格式文件为mysql语法格式文件

./sqlite3-to-mysql.py dump.sql > new.sql

##其中python语法转换脚本内容为：

cat ./sqlite3-to-mysql.py

#! /usr/bin/env python
import re, fileinput, tempfile
from optparse import OptionParser


IGNOREDPREFIXES = [
    'PRAGMA',
    'BEGIN TRANSACTION;',
    'COMMIT;',
    'DELETE FROM sqlite_sequence;',
    'INSERT INTO "sqlite_sequence"',
]

REPLACEMAP = {"INTEGER PRIMARY KEY": "INTEGER AUTO_INCREMENT PRIMARY KEY",
    "AUTOINCREMENT": "AUTO_INCREMENT",
    "DEFAULT 't'": "DEFAULT '1'",
    "DEFAULT 'f'": "DEFAULT '0'",
    ",'t'": ",'1'",
    ",'f'": ",'0'",
}

def _replace_match_allcase(line, src, dst):
    line = line.replace(src,dst)
    line = line.replace(src.lower(),dst)
    return line

def _replace(line):
    if any(line.startswith(prefix) for prefix in IGNOREDPREFIXES):
        return
    for (src,dst) in REPLACEMAP.items():
        line = _replace_match_allcase(line, src, dst)
    return line

def _backticks(line, in_string):
    """Replace double quotes by backticks outside (multiline) strings
    >>> _backticks('''INSERT INTO "table" VALUES ('"string"');''', False)
    ('INSERT INTO `table` VALUES (\\'"string"\\');', False)
    >>> _backticks('''INSERT INTO "table" VALUES ('"Heading''', False)
    ('INSERT INTO `table` VALUES (\\'"Heading', True)
    >>> _backticks('''* "text":http://link.com''', True)
    ('* "text":http://link.com', True)
    >>> _backticks(" ');", True)
    (" ');", False)
    """
    new = ''
    for c in line:
        if not in_string:
            if c == "'":
                in_string = True
            elif c == '"':
                new = new + '`'
                continue
        elif c == "'":
            in_string = False
        new = new + c
    return new, in_string

def _process(opts, lines):
    if opts.database:
        yield '''\
drop database IF EXISTS {d};
create database {d} character set utf8;
grant all on {d}.* to {u}@'localhost' identified by '{p}';
use {d};\n'''.format(d=opts.database, u=opts.username, p=opts.password)
    yield "SET sql_mode='NO_BACKSLASH_ESCAPES';\n"

    in_string = False
    for line in lines:
        if not in_string:
            line = _replace(line)
            if line is None:
                continue
        line, in_string = _backticks(line, in_string)
        yield line

def _removeNewline(line, in_string):
    new = ''
    for c in line:
        if not in_string:
            if c == "'":
                in_string = True
        elif c == "'":
            in_string = False
        elif in_string:
            if c == "\n":
                 new = new + 'Newline333'
                 continue
            if c == "\r":
                 new = new + 'carriagereturn333'
                 continue
        new = new + c
    return new, in_string
	
def _replaceNewline(lines):
    for line in lines:
           line = line.replace("Newline333", "\n")
           line = line.replace("carriagereturn333", "\r")
           yield line

def _Newline(lines):
    in_string = False
    for line in lines:
        if line is None:
           continue
        line, in_string = _removeNewline(line, in_string)
        yield line
	
def main():
    op = OptionParser()
    op.add_option('-d', '--database')
    op.add_option('-u', '--username')
    op.add_option('-p', '--password')
    opts, args = op.parse_args()
    lines = (l for l in fileinput.input(args))
    lines = (l for l in _Newline(lines))
    f = tempfile.TemporaryFile()
    for line in lines:
        f.write(line)
    f.seek(0)
    lines = (l for l in f.readlines())
    f.close()
    lines = (l for l in _process(opts, lines))
    for line in _replaceNewline(lines):
       print line,

if __name__ == "__main__":
    main()

##使用mysql先创建目的库；然后导入转换后的格式文件

mysql -uroot -proot <<EOF
show databases;
drop database IF EXISTS  grafana66;
CREATE DATABASE IF NOT EXISTS grafana66 default charset utf8 COLLATE utf8_general_ci;
quit;
EOF
mysql -uroot -proot -f grafana <  new.sql              ##导入grafana库