MariaDB 学习笔记

安装

• 通常 Centos7 都自带了 MariaDB，可以通过命令行测试

  # mysql -u root
  

• 当 Centos7 安装 epel-release 之后，可通过命令补充安装

  # yum install mariadb mariadb-server -y
  

为 MariaDB 设置初始密码

1. 首先无需密码进入 root 用户

   # mysql -u root
   > MariaDB [(none)]> use mysql;
   

2. 修改密码并退出

   MariaDB [mysql]> update user set password=password("your_password")where user='root'
   MariaDB [mysql]> flush privileges;
   MariaDB [mysql]> exit
   

3. 测试登录

   # mysql -u root -p
   

修改 Root 密码

1. 使用 SET PASSWORD 命令

   mysql> set password for 用户名@localhost = password('新密码'); 
   mysql> set password for root@localhost = password('123');
   

2. 使用 MySQLAdmin

   # mysqladmin -u用户名 -p旧密码 password 新密码 
   # mysqladmin -uroot -p123456 password 123
   

3. 用UPDATE直接编辑user表

   mysql> use mysql; 
   mysql> update user set password=password('123') where user='root' and host='localhost'; 
   mysql> flush privileges;
   

设计一个用户信息表

• 示例代码

  CREATE TABLE `tbl_user` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `user_name` varchar(64) Not NULL DEFAULT '' COMMENT 'USERNAME',
   `user_pwd` varchar(256) NOT NULL DEFAULT '' COMMENT 'ENCODED_PASSWORD',
   `email` varchar(64) DEFAULT '' COMMENT 'EMAIL',
   `phone` varchar(128) DEFAULT '' COMMENT 'PHONE',
   `email_validated` tinyint(1) DEFAULT '' COMMENT 'IF_EMAIL_VERIFIED',
   `phone_validated` tinyint(1) DEFAULT '' COMMENT 'IF_PHONE_VERIFIED',
   `signup_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'REG_TIME',
   `last_active` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'LAST_ACTIVE_TIME',
   `profile` text COMMENT 'USER_PROF',
   `status` int(11) NOT NULL DEFAULT '0' COMMENT 'USER_STATE(ACTIVE/BLOCKLISTED/LOCKED/REMOVED)',
   PRIMARY KEY(`id`),
   UNIQUE KEY `idx_phone` (`phone`),
   KEY `idx_status` (`status`)
  ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
  

简单主从备份

Master 服务器配置

1. 修改 /etc/my.cnf 文件

   [mysqld]
   server-id=1
   log_bin=master-bin
   binlog-ignore-db=mysql
   binlog-ignore-db=information_schema
   binlog-ignore-db=performance_schema
   binlog-ignore-db=test
   innodb_flush_log_at_trx_commit=1
   binlog_format=mixed
   

2. 添加备份账号

   MariaDB [(none)]>  grant replication slave on *.* to 'backup'@'%' identified by 'password';
   MariaDB [(none)]>  flush privileges;
   MariaDB [(none)]>  exit;
   

3. 重启 MariaDB

   # systemctl restart mariadb
   

4. 再次进入 MariaDB 查看 log_bin 文件和 Position

   MariaDB [(none)]>  show master status\G
   

Slave 服务器配置

1. 修改 /etc/my.cnf 文件

   [mysqld]
   server-id=2
   relay-log-index=slave-relay-bin.index
   relay-log=slave-relay-bin
   relay_log_recovery=1
   

2. 重启 MariaDB

   # systemctl restart mariadb
   

3. 配置slave相关参数

   MariaDB [(none)]>  change master to 
                      master_host='master IP',
                      master_user='backup',
                      master_password='123456',
                      master_log_file='master-bin.000001',
   MariaDB [(none)]>  start slave;
   

4. 查看配置是否成功

   MariaDB [(none)]>  show slave status\G
   

参考资料

• MariaDB Server Documentation

​