Shulou(Shulou.com)06/03 Report--

1 mariadb basic command

1. Start mysql

Systemctl start mariadb

The 2.linux client connects to itself

Mysql-uroot-p-h 127.0.0.1

3. Remote link mysql server

Mysql-uroot-p-h 192.168.1.197

Remote authorization:

Grant all privileges on. To root@ "192.168.1.100" identified by "redhat"

Flush privileges

4. Modify mysql password

MariaDB [(none)] > set password = PASSWORD ('redhat123')

5. Create a mysql user

Create user xiaochun@'%' identified by 'xc666'

6. Query user information in mysql library

Use mysql

Select host,user,password from user

7. Authorization statement

Grant the user Li Jun the right to create a database

Mysql uses the grant command to authorize the account. The common format of the grant command is as follows

Grant permissions on database. Table name to account @ hostname authorizes specific tables in a specific database

Grant permissions on database. The to account @ hostname authorizes all tables in a particular database

Grant permissions 1, 2, 3 on. The to account @ hostname gives multiple authorizations to all tables in all libraries

Grant all privileges on. * to account @ hostname authorizes all permissions on all libraries and all tables

# Grant the permission created by Xiaochun, which is effective for all database tables

Grant create on. To xiaochun@ "%" identified by 'xc666'

# Grant Xiaochun user only the permission to create mymysql database

Grant create on mymysql.* to xiaochun@ "%" identified by 'xc666'

# Grant maximum permissions to users, all permissions

Grant all privileges on. To username@'%' identified by 'password'

8. Remove permission

MariaDB [(none)] > revoke all privileges on. From xiaochun@ "%" identified by 'xc666'

9. Backup and recovery of Database

# backup

Mysqldump-u root-p-- all-databases > / tmp/db.sql

# data import in 2 ways

Source / tmp/db.sql

The second kind

Mysql-uroot-p

< /tmp/db.sql 第三种 navicat 第四种，如果你数据量特别大的话，使用第三方工具 xtrabackup 主从复制的7个步骤 主数据库写入数据之后， 会有data changes(数据变化)记录有变化记录之后，将增删改的一些sql语句记录到本地的Binary log(二进制日志)中从库会一直开启着一个线程通过线程去读取这个二进制日志的内容从库会将数据写入到自己的Relay log(中继日志)中从库会将中继日志中的操作转化为SQL thread(SQL语句)通过转化的SQL语句写入到自己的数据库, 两边的数据就一致了 2 mariadb主从复制实验 准备两台数据库服务器 主服务器: 192.168.11.247 从服务器: 192.168.11.246 在主服务器(192.168.11.247)上操作 如果数据库是新安装的数据库的话, 没有中文配置, 还需要添加中文配置 vim /etc/my.cnf [mysqld] character-set-server=utf8 collation-server=utf8_general_ci log-error=/var/log/mysqld.log server-id=1 log-bin=qishi-logbin [client] default-character-set=utf8 [mysql] default-character-set=utf8 启动mariadb systemctl start mariadb 新建用于主从同步的用户xixi, 允许所有ip登录主库 create user 'xixi'@'%' identified by 'xixi666'; 给从库账号授权,给xixi从主库复制的权限 grant relication slave on . to 'xixi'@'%'; 检查主库创建的复制账号 select user,host from mysql.user; 检查授权账号的权限 show grants for xixi@'%'; 对主数据库锁表设置只读, 防止数据写入, 数据复制失败 flush table with read lock; 检查主库的状态,并记录下日志文件的名字和位置 show master status; 记录下主数据库的写入状态和日志文件的名字 锁表后,再单独打开一个ssh窗口, 导出数据库的所有数据, 在主库上导出数据 mysqldump -u root -p --all-databases >

/ opt/lanmaster.sql

Configured on the cluster library (192.168.11.246)

Shut down the database service

Systemctl stop mariadb

Open / etc/my.cf on the cluster library (192.168.11.246)

If it is a newly installed database, you also need to add a Chinese configuration

[mysqld]

Character-set-server=utf8

Collation-server=utf8_general_ci

Log-error=/var/log/mysqld.log

[client]

Default-character-set=utf8

[mysql]

Default-character-set=utf8

Server-id=3

Read-only=true

Restart the database

Systemctl restart mariadb

Copy the database file exported from the master library (192.168.11.247) to the slave library (192.168.11.246)

Scp 192.168.11.247:/opt/lanmaster.sql / opt/

Import the database file from the main database to keep the data of the cluster database consistent with that of the main database

Mysql-u root-p

Source / opt/lanmaster.sql

Configure the parameters replicated by the cluster library (192.168.11.246), and connect the slave cluster library to the configuration of the master main library (192.168.11.247)

Mysql > change master to master_host='192.168.11.247'

Master_user='xixi'

Master_password='xixi666'

Master_log_file='lan-logbin.000001'

Master_log_pos=469

Start the synchronization switch of the cluster library to test the master-slave replication

Start slave

View replication status

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

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.11.247

Master_User: xixi

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: lan-logbin.000002

Read_Master_Log_Pos: 649

Relay_Log_File: mariadb-relay-bin.000003

Relay_Log_Pos: 934

Relay_Master_Log_File: lan-logbin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Note: if you see that the parameters Slave_IO_Running and Slave_SQL_Running are both yes, the master-slave synchronization configuration is successful, otherwise you need to check and reconfigure.

The last operation on the main library

Unlock the write of the main library

Unlock tablse

If the ordinary user above the cluster library cannot log in on the cluster library, recreate a user

Create user ''@'%' identified by' 666'

Grant replication slave on. To ''@'%'

Friendship link

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.