By

Contents
  1. 1. 安装
    1. 1.0.0.1. MariaDB
    2. 1.0.0.2. mysql
    3. 1.0.0.3. 主主互备配置
  2. 1.0.1. 导出数据
  3. 1.0.2. 执行命令
  4. 1.0.3. 备份数据库
  5. 1.0.4. 恢复数据库
  6. 1.0.5. 用户管理
  • 2. 库表数据量查看

    • 安装

    MariaDB

    https://mariadb.com/kb/en/yum/

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    vi /etc/yum.repos.d/MariaDB.repo

    [mariadb]
    name = MariaDB-10.3
    baseurl=http://142.4.219.197/10.3/centos7-amd64
    # alternative: baseurl=http://archive.mariadb.org/mariadb-10.3.14/yum/centos7-amd64
    gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
    gpgcheck=0

    yum install MariaDB-server

    service  mariadb  start 
    sudo mysql_secure_installation

    systemctl start mariadb #启动服务
    systemctl enable mariadb #设置开机启动
    systemctl restart mariadb #重新启动
    systemctl stop mariadb.service #停止MariaDB

    mysql

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    yum install mysql-server

    sudo rpm -ivh mysql-community-common-5.7.38-1.el7.x86_64.rpm 
    sudo rpm -ivh mysql-community-libs-5.7.38-1.el7.x86_64.rpm
    sudo rpm -ivh mysql-community-client-5.7.38-1.el7.x86_64.rpm 
    sudo rpm -ivh mysql-community-server-5.7.38-1.el7.x86_64.rpm 

    sudo systemctl enable mysqld
    sudo service mysqld start
    sudo grep 'temporary password' /var/log/mysqld.log

    mysql  -uroot -p
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

    mysql>create user ds@localhost identified by '123';
    mysql>grant all on *.* to ds@localhost identified by '123';
    mysql>grant all privileges on *.* to ds@'%' ;
    mysql>grant select on *.* to ds@'hostname' identified by '123' with grant option;
    MariaDB [mysql]> select host,user,password from mysql.user;

    mysql>flush privileges;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    # ubuntu - mysql8
    sudo apt install mysql-server
    sudo cat /etc/mysql/debian.cnf #密码
    mysql -u  debian-sys-maint -p
    use mysql;
    ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '123456';
    FLUSH PRIVILEGES;

    sudo vi /etc/mysql/my.cnf

    [mysqld]
    bind-address=0.0.0.0

    Public Key Retrieval is not allowed解决方法：

    1. ALTER USER ‘root‘@’%’ IDENTIFIED WITH mysql_native_password BY ‘root’;
    2. 连接url增加参数 ?allowPublicKeyRetrieval=true

    https://www.cnblogs.com/river2005/p/6813618.html

    主主互备配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    vi /etc/my.cnf   /etc/my.cnf.d/mariadb-server.cnf

    server-id = 1           
    auto-increment-offset = 1    
    auto-increment-increment = 2  
    log-bin = mysql-bin     
    sync_binlog = 1
    binlog_checksum = none
    binlog_format = mixed 
    slave-skip-errors = all

    server-id = 2        
    auto-increment-offset = 2    
    auto-increment-increment = 2    
    log-bin = mysql-bin    
    sync_binlog = 1
    binlog_checksum = none
    binlog_format = mixed 
    slave-skip-errors = all

    #保存配置文件并重启mysql
    service mariadb restart
    sudo service mysqld restart

    #用mysql控制台登录并授权从库可以读取主库的二进制日志
    mysql -u root -p
    mysql>grant replication slave,replication client on *.* to root@'192.168.56.108' identified by "root";
    mysql>flush privileges;

    #用mysql控制台登录并授权从库可以读取主库的二进制日志
    mysql -u root -p
    mysql>grant replication slave,replication client on *.* to root@'192.168.56.109' identified by "root";

    mysql>flush privileges;

    #查看下log bin日志和pos值位置，并记录File 和Position的值
    mysql> show master status;

    change  master to master_host='192.168.56.108',master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=373;   
    start slave;
    show slave status\G;
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes


    change  master to master_host='192.168.56.109',master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=373;   
    start slave;
    show slave status\G;
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

    #stop slave;
    #flush logs;

    ====
    create database test;

    use test;

    CREATE TABLE IF NOT EXISTS `runoob_tbl`(
    `runoob_id` INT UNSIGNED AUTO_INCREMENT,
    `runoob_title` VARCHAR(100) NOT NULL,
    `runoob_author` VARCHAR(40) NOT NULL,
    `submission_date` DATE,
    PRIMARY KEY ( `runoob_id` )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

    show tables;

    INSERT INTO runoob_tbl
    (runoob_title, runoob_author, submission_date)
    VALUES
    ("学习 PHP", "菜鸟教程", NOW());


    INSERT INTO runoob_tbl
    (runoob_title, runoob_author, submission_date)
    VALUES
    ("JAVA 教程", "RUNOOB.COM", '2016-05-06');


    select * from runoob_tbl;

    drop table runoob_tbl;
    drop database test;

    导出数据

    mysql -u root -h 10.3.4.194 -p -e “select * from dolphinscheduler.t_ds_user;” > userinfo.log

    执行命令

    mysql -u root -h 10.3.4.194 -p -e “drop database dolphinscheduler;”

    备份数据库

    mysqldump -h10.3.4.194 -P3306 -uroot -p –databases dolphinscheduler > dolphinscheduler.sql;

    恢复数据库

    mysql -u root -h 10.3.4.194 -p < dolphinscheduler.sql

    用户管理

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    create user 'admin'@'localhost' IDENTIFIED BY 'Admin123!';

    GRANT  ALL PRIVILEGES  ON *.* TO 'admin'@'localhost'

    ALTER USER 'demo'@'localhost' ACCOUNT UNLOCK;

    ALTER USER 'backup_user'@'%' ACCOUNT LOCK;

    ALTER USER 'root'@'%' IDENTIFIED BY 'vs jw';

    DROP USER 'root'@'%';

    库表数据量查看

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    -- 统计MySQL指定数据库中有多少张表
    SELECT count(*) TABLES, table_schema FROM information_schema.TABLES
    GROUP BY table_schema;
     
    -- 统计MySQL指定数据库中表数据量
    use ;
    select table_name,table_rows 
    from information_schema.tables 
    where TABLE_SCHEMA = '数据库名称' 
    order by table_rows desc;