Classified directory archiving: MariaDB

MariaDB 10.5+One click installation script

Comment

Script properties:

  • Only install MariaDB 10+and its dependencies, and do not install others to reduce disk space occupation;
  • The script supports CentOS 7~8 and Red Hat 7~8 system installation;
  • The script supports high customization, which can be customized as required;
  • The MariaDB 10.5+installation mode is binary (the default installation version is Mariadb-10.5.5 x64).

How to install MariaDB 10.5+:

Copy and paste the following code to the terminal and press Enter:

 curl -o-  https://renwole.com/sh/install_mariadb-10.5.sh  | bash

Script instructions:

There are two variable values in the script that can be modified as needed, namely, the MariaDB version and the MariaDB password. Others do not need to be modified. MariaDB will be installed in /apps/server/mariadb Directory, data files are stored in  /apps/server/mariadb/data Location.

For example, if you need to install MariaDB 10.5.4, you only need to replace 10.5.5 with the required version. This method is applicable to all versions of MariaDB 10.5+.

 mariadb_version="10.5.4" mariadb_password="Renwole.com#Node"

notes : After the installation is successful, the account password information will be printed on the screen.
In addition, if the relevant version of MariaDB has been installed, please uninstall it first and delete the startup script, otherwise the installation may fail.

Special attention: When selecting a version, please confirm
https://mirrors.tuna.tsinghua.edu.cn/mariadb/
 http://mirrors.ustc.edu.cn If there is a corresponding version in, otherwise the installation will fail.

One click backup script of Web MySQL

Comment

Script property description:

  • Backup website data/database data to remote FTP server;
  • Support CentOS/Fedora/Ubuntu/Debian system;
  • Support crontab scheduled backup;
 #!/ bin/env bash #Install FTP tools according to the system version command -v yum >/dev/null 2>&1 && apt-get -y install ftp command -v apt-get >/dev/null 2>&1 && yum -y install ftp #Database name/account/password #Fill in according to the actual information DBName=" renwole " DBUser=" renwole " DBPass=" Renwole1!@# " #Ftp remote host/port/account/password #Fill in according to the actual information FtpHost=" renwole.com " FtpPort=" twenty-one " FtpUser=" renwole " FtpPass=" Renwole1!@# " FtpDir=" /wwwroot/Renwolecom " #Web name/directory and backup path #Modify according to the actual web path WebName=" Renwole " WebDir=" /apps/web/renwole.com " mkdir /backup BakDir="/backup" #Package, backup and upload the database and website data to the ftp server command -v mysqldump >/dev/null 2>&1 || { echo "Not found MySQL/MariaDB ENV"; kill -9 $$; } mysqldump -u${DBUser} -p${DBPass} ${DBName} >${BakDir}/$ {DBName}- $(date +"%Y%m%d").sql tar zcf ${BakDir}/$ {WebName}- $(date +"%Y%m%d").tar.gz ${WebDir} ftp -v -n ${FtpHost} ${FtpPort}<< EOF user ${FtpUser} ${FtpPass} type binary passive cd ${FtpDir} put ${BakDir}/$ {DBName}- $(date +"%Y%m%d").sql put ${BakDir}/$ {WebName}- $(date +"%Y%m%d").tar.gz bye EOF

Download the data backup script:

 curl -O  https://renwole.com/sh/backup.sh  && chmod +x backup.sh

Set crontab scheduled task:

 #Backup at 2:00 am every day and push it to the FTP server echo "0 0 2 * * /backup/backup.sh " >> /var/spool/cron/root

notes : Content annotated gules Some of them need to be modified according to their own actual information , others do not need to be modified. In addition, you can also modify ftp and support the sftp security backup mode. Or delete ftp and only back up the data to the local specified directory.

MySQL is automatically backed up and submitted to the CodeCloud Git warehouse

Comment

The reason for choosing Code Cloud is that its private warehouse is free, and GitHub Private warehouses are charged, about $7+a month, which seems to be a big expense for individual developers.

Automatically upload the backup to the code cloud when creating Git Before the warehouse, we suggest you read the following articles:

《 How to push local projects to the code cloud or GitHub through Git 》

Not very familiar with Git This article is very useful to our friends. Otherwise, various errors will be reported during the following steps.

Implementation scheme:

    • Log in to BitCloud
    • Create private warehouse
    • The server generates the ssh public key
    • Create SSH public key

The above steps are described in detail above.

1. Create shell script file:

 $ cd /mnt/renwole $ vim mysqlbak.sh

2. Add the following:

 #!/ bin/bash createAt=`date +%Y-%m-%d-%H:%M:%S` mysql_back_path=/mnt/MySQL-Bak /usr/local/mysql/bin/mysqldump -u Database user name -p password  Database name > $mysql_back_path/renwoleblog-$createAt.sql #Automatically delete backups older than 7 days #- type file type f is a file find $mysql_back_path -name "*.sql" -type f -mtime +7 -exec rm -rf {} \; cd $mysql_back_path git add -A git commit -m "${createAt}" git push origin master

be careful: The above path must be an absolute path, otherwise execute crontab after mysqldump Succeeded, but the export result is empty. Manual execution is normal!

3. Set execution permission:

 $ chmod +x mysqlbak.sh

4. Add task plan:

adopt crontab Scheduled execution of backup scripts

 $ crontab -e

Insert the following:

 */50 2 * * * /mnt/renwole/mysqlbak.sh
 $ systemctl restart crond

After the setting is completed, automatic backup will be performed at 2:50 every morning mysql Database and push to the code cloud git Warehouse, which can be used later Git View the historical submitted version. Don't worry about data loss anymore.

Optimize and repair damaged table files of MariaDB (MySQL) database

Comment

Usually, when maintaining the website, you will encounter a data table crash, which may be MySQL It is not caused by normal shutdown or power failure. At this time, we need to repair the table file manually, usually through database management tools, such as: phpmyadmin Visual repair. Now I will explain Linux Use the command to repair the damaged table file.

The following operations are based on the WordPress Program demonstration. If some tables have problems, they can be repaired or optimized.

Log in to the MariaDB database

 $ mysql -u root -p Enter password: [Enter your MySQL or database management password] Welcome to the MariaDB monitor.   Commands end with ;  or \g. Your MariaDB connection id is 84162 Server version: 10.3-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;'  or '\h' for help.  Type '\c' to clear the current input statement. MariaDB [(none)]> use renwolecomdb;

To view all current datasheet files:

 MariaDB [renwolecomdb]> show tables; +------------------------+ | Tables_in_renwolecomdb  | +------------------------+ | wp_baidusubmit_sitemap | | wp_baidusubmit_urlstat | | wp_commentmeta         | | wp_comments            | | wp_links               | | wp_options             | | wp_postmeta            | | wp_posts               | | wp_term_relationships  | | wp_term_taxonomy       | | wp_termmeta            | | wp_terms               | | wp_usermeta            | | wp_users               | +------------------------+ 14 rows in set (0.00 sec)

If you feel that a table in your database is loading slowly, you can optimize the data table. The command is as follows:

 MariaDB [renwolecomdb]> OPTIMIZE TABLE wp_baidusubmit_urlstat; +------------------------------------+----------+----------+----------+ | Table                              | Op       | Msg_type | Msg_text | +------------------------------------+----------+----------+----------+ |renwolecomdb.wp_baidusubmit_urlstat | optimize | status   | OK       | +------------------------------------+----------+----------+----------+ 1 row in set (0.11 sec)

The detailed analysis report has been printed above.

The damaged table file will be repaired as follows:

 MariaDB [renwolecomdb]> REPAIR TABLE wp_baidusubmit_sitemap; +------------------------------------+--------+----------+----------+ | Table                              | Op     | Msg_type | Msg_text | +------------------------------------+--------+----------+----------+ |renwolecomdb.wp_baidusubmit_sitemap | repair | status   | OK       | +------------------------------------+--------+----------+----------+ 1 row in set (0.00 sec)

Repair succeeded.

Repair the InnoDB engine table file with the following command:

 MariaDB [renwolecomdb]> REPAIR TABLE wp_commentmeta; +----------------------------+--------+----------+---------------------------------------------------------+ | Table                      | Op     | Msg_type | Msg_text                                                | +----------------------------+--------+----------+---------------------------------------------------------+ |renwolecomdb.wp_commentmeta | repair | note     | The storage engine for the table doesn't support repair | +----------------------------+--------+----------+---------------------------------------------------------+ 1 row in set (0.00 sec)

Prompt error report: The storage engine for the table doesn't support repair.

The above error indicates that the database InnoDB The engine data table does not support repair.

However, it should be noted that:

InnoDB The chance of damaging the table is very small, because InnoDB First write to the log, then write to the database, so InnoDB Engine ratio MyISAM It is much stronger, and it has the ability of self repair. Generally, once InnoDB The data file of is damaged. You can only find a backup to restore it.

Therefore, we must back up, back up, and back up the data in case of operation and maintenance.

Stop the solution immediately after MySQL 5.8 (MariaDB) is started

Comment

The installation is smooth. In addition to the last step, start MySQL:

 $ mysqld –skip-grant-tables –user=mysql

The following error message is printed and then stopped:

[ERROR] Can’t find messagefile ‘/usr/share/errmsg.sys’

After Google search, I found a way to start by adding the following parameters:

–lc-messages-dir=”/usr/local/mysql/share/english/

To start it and make it work, I used the following command:

 $ mysqld –skip-grant-tables –user=mysql –lc-messages-dir=”/usr/local/mysql/share/english/

Finally, it started successfully.

MariaDB MaxScale 2.1.3 Read write separation installation configuration of middleware database

1 reply

Operating system: CentOS Linux release 7.3.1611 (Core)
Database: MariaDB-10.2.6-linux-glibc_214-x86_64
MaxScale server: 10.200.10.55
Master server: 172.16.8.56
Slave server: 172.16.8.57
Slave server: 172.16.8.58

1. There are many installation methods of maxscale, such as source code installation, rpm, binary build, etc. I choose binary to install.

Download the corresponding version and download address according to the scenario needs// mariadb.com/downloads/maxscale

 [ root@localhost  ~]# groupadd maxscale [ root@localhost  ~]# useradd -g maxscale maxscale [ root@localhost  ~]# cd /usr/local [ root@localhost  local]# wget //downloads.mariadb.com/MaxScale/2.1.3/centos/7server/x86_64/maxscale-2.1.3.centos.7.tar.gz [ root@localhost  local]# tar zxvf maxscale-2.1.3.centos.7.tar.gz [ root@localhost  local]# ln -s maxscale-2.1.3.centos.7 maxscale [ root@localhost  local]# cd maxscale [ root@zhu56  maxscale]# chown -R maxscale var

It is recommended to create a soft connection, which is helpful for future version upgrading and later maintenance.

2. To install maxscale for the first time, you need to create a log related directory

 [ root@localhost  ~]# mkdir /var/log/maxscale [ root@localhost  ~]# mkdir /var/lib/maxscale [ root@localhost  ~]# mkdir /var/run/maxscale [ root@localhost  ~]# mkdir /var/cache/maxscale

3. The following directories must have maxscala user permissions

 [ root@localhost  ~]# chown maxscale /var/log/maxscale [ root@localhost  ~]# chown maxscale /var/lib/maxscale [ root@localhost  ~]# chown maxscale /var/run/maxscale [ root@localhost  ~]# chown maxscale /var/cache/maxscale

4. In order to enable Maxscale to start successfully, it is also necessary to create a configuration file. Copy the configuration file template in the Maxscale directory to ETC.

 [ root@localhost  ~]# cp /usr/local/maxscale/etc/maxscale.cnf.template /etc/maxscale.cnf

5. Before modifying the configuration file, a user needs to be created and authorized on the main server, and this user is used for MySQL monitoring and routing functions

 MariaDB [(none)]> create user 'jiankongdb'@'%' identified by 'jiankong123'; MariaDB [(none)]> grant SELECT on mysql.user to 'jiankongdb'@'%'; MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'jiankongdb'@'%'; MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO 'jiankongdb'@'%'; MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO 'jiankongdb'@'%'; MariaDB [(none)]> grant REPLICATION CLIENT on *.* to 'jiankongdb'@'%'; MariaDB [(none)]> GRANT replication slave, replication client,SELECT ON *.* TO jiankongdb@'%';

6. View authorization

 MariaDB [(none)]> SHOW GRANTS FOR'jiankong'@'%';

7. Next, start to modify the maxscale.cnf configuration file, otherwise it will fail to start.

 [ root@localhost  ~]# vim /etc/maxscale.cnf # MaxScale documentation on GitHub: # //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md # Global parameters # # Complete list of configuration options: # //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md #Global configuration [maxscale] threads=1 # Server definitions # # Set the address of the server to the network # address of a MySQL server. # [server1] type=server address=172.16.8.56 port=3306 protocol=MySQLBackend serv_weight=1 [server2] type=server address=172.16.8.57 port=3306 protocol=MySQLBackend serv_weight=3 [server3] type=server address=172.16.8.58 port=3306 protocol=MySQLBackend serv_weight=3 # Monitor for the servers # # This will keep MaxScale aware of the state of the servers. # MySQL Monitor documentation: # //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md #MariaDB status monitoring [MySQL Monitor] type=monitor module=mysqlmon servers=server1,server2,server3 user=jiankong passwd=jiankong123 monitor_interval=10000 Detect_stale_master=true # Even if the slave fails, ensure that the master is responsible for reading and writing # Service definitions # # Service Definition for a read-only service and # a read/write splitting service. # # ReadConnRoute documentation: # //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md #Read [Read-Only Service] type=service router=readconnroute servers=server1,server2,server3 user=jiankong passwd=jiankong123 router_options=slave Enable_root_user=1 # Allow root user to log in and execute Weightby=serv_weight # Master slave weight # ReadWriteSplit documentation: # //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadWriteSplit.md #Write [Read-Write Service] type=service router=readwritesplit servers=server1,server2,server3 user=jiankong passwd=jiankong123 max_slave_connections=100% Use_sql_variables_in=master # Ensure session consistency Enable_root_user=1 # Allow root login Max_slave_replication_lag=3600 # Allow slave synchronization time to exceed the master synchronization time, and do not route if it exceeds the master synchronization time # This service enables the use of the MaxAdmin interface # MaxScale administration guide: # //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md [MaxAdmin Service] type=service router=cli # Listener definitions for the services # # These listeners represent the ports the # services will listen on. # [Read-Only Listener] type=listener service=Read-Only Service protocol=MySQLClient port=4008 [Read-Write Listener] type=listener service=Read-Write Service protocol=MySQLClient port=4006 [MaxAdmin Listener] type=listener service=MaxAdmin Service protocol=maxscaled socket=default

Save and exit.
8. Create a startup script below

 [ root@localhost  ~]# cp /usr/local/maxscale-2.1.3.centos.7/share/maxscale.service /usr/lib/systemd/system/ [ root@localhost  ~]# vim /usr/lib/systemd/system/maxscale.service

9. Modify ExecStart=///bin/maxscale in maxscale.service to ExecStart=/usr/local/maxscale/bin/maxscale

 [ root@localhost  ~]# chmod 755 /usr/lib/systemd/system/maxscale.service [ root@localhost  ~]# systemctl enable maxscale [ root@localhost  ~]# systemctl daemon-reload [ root@localhost  ~]# systemctl start maxscale

10. Add variable value

 [ root@localhost ~]#Vi/etc/profile//Add the following content to the last line and exit! PATH=$PATH:/usr/local/maxscale/bin export PATH [ root@localhost ~]#Source/etc/profile//Make its variables take effect immediately

11. Next, you can use MaxAdmin for management. MaxAdmin is a simple client management interface, which can be used to interact with the MariaDB MaxScale server. It can display the internal statistics status of the MariaDB MaxScale and control the operations of the MariaDB MaxScale. Details:
//mariadb.com/kb/en/mariadb-enterprise/maxadmin-admin-interface/

 [ root@localhost ~]#Maxadmin//Enter MaxScale> list servers Servers. ---------------+--------------+-------+-------------+----------------- Server | Address | Port | Connections | Status  ---------------+--------------+-------+-------------+----------------- server1 | 172.16.8.56 | 3306 | 0 | Master, Running server2 | 172.16.8.57 | 3306 | 0 | Slave, Running server2 | 172.16.8.58 | 3306 | 0 | Slave, Running ---------------+--------------+-------+-------------+-----------------

12. MaxScale has been configured. Now you can use the client to connect to the Maxscale server port 4006.

MySQL (MariaDB) database cracking root passwords of different versions under Windows

Comment

Method 1: (applicable to MySQL 5.0/5.1/5.2/5.5)

Start - Run - cmd Enter the following command to stop MySQL, or Start - Run - services.msc to find and stop the MySQL service.

 D:\MySQL\MySQL Server 5.2\bin>Net stop mysql

Go to the specific installation path of MySQL under the BIN directory and execute:

 D:\MySQL\MySQL Server 5.2\bin>mysqld-nt --skip-grant-tables or D:\MySQL\MySQL Server 5.2\bin>mysqld --skip-grant-tables

The current window will stop.

Re open the cmd command window to the bin directory of mysql and run mysql directly

 D:\MySQL\MySQL Server 5.2\bin>mysql D:\MySQL\MySQL Server 5.2\bin>use mysql; D:\MySQL\MySQL Server 5.2\bin>update user set password=password("RenwoleNEWPass") where user="root"; D:\MySQL\MySQL Server 5.2\bin>flush privileges; D:\MySQL\MySQL Server 5.2\bin>exit;

Scheme II; (Applicable to mysql5.6/5.7/8.0)
Execute the following operations under the MySQL installation directory BIN;

 D:\MySQL\MySQL Server 5.6\bin>Net stop mysql D:\MySQL\MySQL Server 5.6\bin>mysqld --skip-grant-tables D:\MySQL\MySQL Server 5.6\bin>use mysql; D:\MySQL\MySQL Server 5.6\bin>update mysql.user set authentication_string=password('RenwoleNEWPass') where user='root'; D:\MySQL\MySQL Server 5.6\bin>flush privileges; D:\MySQL\MySQL Server 5.6\bin>exit;

Finally, end the MySQL process and restart the MySQL service.

Linux Centos7 MariaDB (MySQL) 10.2 Master Slave Semi Synchronous Replication Mode Installation Configuration

Comment

Experimental environment;

Operating system: CentOS Linux release 7.3.1611 (Core)
Database: MariaDB-10.2.6-linux-glibc_214-x86_64

1. To implement the semi synchronous replication function, you need to install the plug-ins provided by Google on the master and slave servers. The plug-ins are in the MySQL binary/usr/local/mysql/lib/plugin directory;

Main plug-in: semisync_master.so
From plug-in: sosemisync_slave.so

2. Install plug-ins on the master and slave nodes respectively

3. Main server installation plug-in:

 MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; // Installing plug-ins MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled = 1; // Start plug-in module MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout = 1000; // Set timeout

4. Install plug-ins from the server;

 MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; // Installing plug-ins MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled = 1; // Start plug-in module MariaDB [(none)]> STOP SLAVE IO_THREAD;  START SLAVE IO_THREAD; // Restart the process to make the plug-in module take effect

5. Edit and add the following in the main master and slave my.cnf:

main

[mysqld]
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000

from

[mysqld]
rpl_semi_sync_slave_enabled = 1

6. So far, we have implemented the semi synchronous architecture of MySQL database replication. Create a database or table in the database of the primary server. After the secondary server is stopped, check whether the primary server is delayed for the first time. The secondary database creation is reduced to asynchronous synchronization. If the synchronization is successful after the secondary server is started, it indicates that the configuration is OK.

Linux MariaDB (MySQL) 10.2 database installation configuration based on GTID master-slave synchronous replication

5 replies

Original works, please indicate the address of this article for reprinting

Operating system: CentOS Linux release 7.3.1611 (Core)
Database: MariaDB-10.2.6-linux-glibc_214-x86_64

Main server: 10.10.10.56
Slave server: 10.10.10.163

1. One way master slave synchronization is what we need to do today

For the installation of the MariaDB (MySQL) database, see Centos7.3 x64 Bit Binary Installation (MySQL) MariaDB 10.1.20 Database Production Next, start the configuration process of the MariaDB (MySQL) server cluster.

2. Modify or add the following information in the MariaDB (MySQL) configuration file

vim /etc/my.cnf

Master slave general configuration

 Binlog format=mixed # The mode of binary logging (enabled by default for higher versions) Binlog checksum=CRC32 # Enables the host to write verification for events written to the binary log (enabled by default for higher versions) Sync master info=1 # MariaDB relies on the operating system to refresh the master.info file to disk. Sync_relay_log_info=1 # MariaDB relies on the operating system to refresh the relay-log.info file to the disk. Expire_logs_days=7 # The number of days the log file expires. The default value is 0, indicating that it does not expire Master verify checksum=1 # master server validation Slave sql verify checksum=1 # Validate from server

3. In addition to the general configuration, the master server also needs to add the following codes

 Server id=56 # MySQL server ID, not duplicate Log bin=mysql bin # Binary log (enabled by default) Sync binlog=1 # set by the master server for transaction security log-bin-index = mysql-bin

4. In addition to general configuration, slave server slave also needs to add the following code

 server-id = 163 Relay log=relay bin # Relay log Slave parallel threads=2 # Set the number of SQL threads from the server #Replicate do db=renwoleblogdb # Replicate the specified database and write multiple rows Replicate ignore db=mysql # Database not backed up, multiple writes and multiple rows Relay_log_recovery=1 # It can be used after the slave crashes to prevent damaged relay log processing. Log slave updates=1 # slave writes replication events into its own binary log relay-log-index = relay-bin

In addition, it is not necessary to enable binary logs from the MySQL slave server, but in some cases, it must be set, for example; If slave is the master of other slave, bin_log must be set. I will start it by default.

5. The above is just a brief introduction of the role of each parameter. The specific settings of these parameters need to be adjusted according to the actual situation of the user, and the details can be learned from the official

Replication and Binary Log Server System Variables
//mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/

For the compatibility of system variables, see the official

MariaDB and MySQL Compatibility
//mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/

6. Master authorization configuration of master server

Create a special account on the primary MariaDB server and authorize database permissions, as well as remote access from the server IP

 # mysql -uroot -p Enter password: [Enter your MySQL password] MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'renwoleuseracc'@'%' IDENTIFIED BY 'renwoleuserpass'; // Create a special backup account for Slave MariaDB [(none)]> flush privileges; // Refresh MySQL permissions MariaDB [(none)]> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; // View authorization MariaDB [(none)]> flush tables with read lock; // Lock the database to prevent the master value from changing MariaDB [(none)]> show master status; // Get master status value +-----------------+----------+------------+-----------------+ | File |Position |Binlog_Do_DB|Binlog_Ignore_DB | +-----------------+----------+------------+-----------------+ | mysql-bin.000006| 627 | | | +-----------------+----------+------------+-----------------+ 1 row in set (0.00 sec)

7. Once the correct Binlog bit (file name and offset) at the time of backup is obtained, the BINLOG_GTID_POS() function can be used to calculate the GTID

 MariaDB [(none)]> SELECT BINLOG_GTID_POS("mysql-bin.000006", 627); +------------------------------------------+ | BINLOG_GTID_POS('mysql-bin.000006', 627) | +------------------------------------------+ | 0-56-4 | +------------------------------------------+ 1 row in set (0.01 sec)

8. Slave configuration

As the official said, starting from the MariaDB 10.0.13 version, mysqldump will automatically complete this work, and write the GTID in the export file, as long as you set – master data or – dump slave and set – gtid at the same time.

In this way, the new SLAVE can set the start position of replication by setting the value of @ @ gtid_slave_pos, use CHANGE MASTER to transfer this value to the main database, and then start replication:

 # mysql -uroot -p Enter password: [Enter your MySQL password] MariaDB [(none)]> SET GLOBAL gtid_slave_pos = "0-56-4"; MariaDB [(none)]> change master to master_host='10.10.10.56',MASTER_PORT = 3306,master_user='renwoleuseracc',master_password='renwoleuserpass',master_use_gtid=slave_pos; // Perform master-slave authorization MariaDB [(none)]> START SLAVE; // Start Slave MariaDB [(none)]> show slave status\G *************************** 1.  row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.56 Master_User: renwoleuseracc Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 627 Relay_Log_File: relay.000035 Relay_Log_Pos: 537 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... ... ... Using_Gtid: Slave_pos Gtid_IO_Pos: 0-56-4

9. If Slave_IO_Running and Slave_SQL_Running are both YES, it indicates that the service has been run, and the Using_Gtid column determines whether the GTID values are consistent.
explain:

Master_host indicates the authorized address of the master
MASTER_PORT MySQL port
Master_user indicates the master authorization account
Master_password indicates the password
Master_use_gtid GTID variable value

10. Next, unlock the primary server database table

 MariaDB [(none)]> unlock tables; // Unlock Data Table MariaDB [(none)]> show slave hosts; // View slave server connection status MariaDB [(none)]> show global status like "rpl%"; // View client

11. View all relevant parameters of relay from the server Slave

 MariaDB [(none)]> show variables like '%relay%';

12. The master-slave configuration has been completed. Now whether you add, modify, delete, or check on the primary server, you will synchronize it to the secondary server, and you can conduct relevant tests according to your own needs.
About the reset syntax of master slave

Reset the core syntax of master

RESET MASTER; This means that executing the RESET MASTER will delete all binary log files and create a blank binary log file with a number suffix of. 000001. The RESET MASTER will not affect the working status of the SLAVE server. Therefore, executing this command will cause Slave to fail to find the master's binlog, which will cause synchronization failure.

Reset slave's core syntax

RESET SLAVE; Means; RESET SLAVE will clear the synchronization location on the slave and delete all old synchronization relay log files, but the slave service must be stopped before resetting (STOP SLAVE)

When I have time, I will introduce the GTID based semi synchronous master-slave article, which is also required to be recorded in production for future use.

More information://mysql.taobao.org/monthly/2016/02/08/

Linux MariaDB (MySQL) database changes user permissions

Comment

When maintaining the MariaDB (MySQL) database server at ordinary times, it is inevitable to use some common commands. The MariaDB database will not have problems for a long time, and some SQL statements will be forgotten. They have not been recorded before. Today, if you have nothing to do, they will record it and share it with you. I hope you will understand the shortcomings.

This article is applicable to all versions of MariaDB and MySQL 5.2 or above
Production environment Centos7.3 64 bit, MariaDB server 10.2.5

The simple format of the command that MariaDB grants user permissions can be summarized as follows:

Grant permission on database object to user;

 #MySQL - u root - p//Log in to the database Enter password: MariaDB [(none)]> show databases; // View all databases in the current database MariaDB [(none)]> create database renwole; // Create a new database named "renwole" MariaDB [renwoleBD]> show tables; // Display table files in a database MariaDB [(none)]> select version(),current_date; // View database version and current date MariaDB [(none)]> drop database renwole; // Delete renwole database MariaDB [renwoleDB]>desc table name// View database table structure MariaDB [(none)]> show variables like '%dir%'; // View database storage path MariaDB [(none)]> show grants;  View current user permissions MariaDB [(none)]> show grants for root@'localhost'; // view user permission

The following explains the MariaDB database instance, for example:

Create and add a renwole user with the password renwole123

 #MySQL - u root - p//Log in to the database Enter password: MariaDB [(none)]> insert into mysql.user(Host,User,Password) values("localhost","renwole",password("renwole123"));

Create a renwoleDB database and authorize the user renwole to have all the permissions of the database.

 MariaDB [(none)]> create database renwoleDB; // newly build MariaDB [(none)]> grant all privileges on renwoleDB.* to renwole@% identified by 'renwole123'; // Authorization. The keyword "privileges" can be omitted. MariaDB [(none)]> flush privileges; // Refresh user permissions

If you want to give a user the right to query, insert, update, and delete all table data in the database, you can write as follows:

 MariaDB [(none)]>grant select on renwoleDB. * to renwole @ '%'//Query MariaDB [(none)]>grant insert on renwoleDB. * to renwole @ '%'//Insert MariaDB [(none)]>grant update on on renwoleDB. * to renwole @ '%'//Update MariaDB [(none)]>grant delete on renwoleDB. * to renwole @ '%'//Delete

Or, replace it with a statement command:

 MariaDB [(none)]> grant select,insert,delete,update on renwoleDB.* to renwole@’%’ identified by 'renwole123';

If you want to view the permissions of all MySQL users, the code is as follows:;

 MariaDB [(none)]> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

If you want to delete a user and permission, you can write it this way;

 MariaDB [(none)]> drop user  renwole@localhost ;

If you want to change a user password;

 MariaDB [(none)]> update mysql.user set password=password('New-password') where User="renwole" and Host="%"; MariaDB [(none)]> flush privileges;

Add the entire MySQL server permissions of the advanced root user

 grant all on *.* to root@'%' identified by 'Password';

Note: The [identified by] sentence can be used to set the password. If the user's password is not specified, it will remain unchanged.

Revoke the permissions that have been granted to the MariaDB user.
The syntax of revoke is similar to grant. Just change the keyword "to" to "to" from, for example:

 grant all on *.* to renwole@%; revoke all on *.* from renwole@%;

Precautions for grant and revoke user permissions of the MariaDB database; Grant, revoke the user's permission, and the user's permission will take effect only after reconnecting to the MySQL database. If you want authorized users to grant these permissions to other users, you need to select "grant option", for example:

 MariaDB [(none)]> grant select on renwoleDB.* to renwole@% with grant option;

This feature is not commonly used. In fact, the database permissions should be managed uniformly by the root user.

Note: Sometimes renwole @ '%' requires a single quotation mark when authorizing any host connection, but sometimes it does not.