search

hoangdh / ghichep-database

Ghi chép về các kỹ thuật/giải pháp HA cho mysql/mariadb. Cấu hình Galera Cluster cho MariaDB trên Linux.
17 stars 32 forks source link

Một vài ghi chép nhỏ về Galera - MariaDB, MySQL #1

Open hoangdh opened 6 years ago

hoangdh commented 6 years ago

Sửa lỗi database bị crash

190326 10:49:24 [ERROR] /usr/sbin/mysqld: Table './mysql/user' is marked as crashed and should be repaired
190326 10:49:24 [ERROR] /usr/sbin/mysqld: Table './mysql/user' is marked as crashed and should be repaired
service mysql start –skip-grant-tables
mysqlcheck -r mysql user
services mysql stop
services mysql start

HOẶC

Đăng nhập vào console MySQL

mysql> use mysql;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> check table user;
+————+——-+———-+———————————————————-+
| Table | Op | Msg_type | Msg_text |
+————+——-+———-+———————————————————-+
| mysql.user | check | warning | Table is marked as crashed |
| mysql.user | check | warning | 6 clients are using or haven’t closed the table properly |
| mysql.user | check | error | Record at pos: 24992 is not remove-marked |
| mysql.user | check | error | record delete-link-chain corrupted |
| mysql.user | check | error | Corrupt |
+————+——-+———-+———————————————————-+
5 rows in set (0.02 sec)

mysql> repair table user;
+————+——–+———-+——————————————+
| Table | Op | Msg_type | Msg_text |
+————+——–+———-+——————————————+
| mysql.user | repair | warning | Number of rows changed from 1384 to 1385 |
| mysql.user | repair | status | OK |
+————+——–+———-+——————————————+
2 rows in set (0.48 sec)

mysql> check table user;
+————+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————+——-+———-+———-+
| mysql.user | check | status | OK |
+————+——-+———-+———-+
1 row in set (0.02 sec)

mysql>

#service mysql start

Đổi port mặc định của MySQL/MariaDB

  1. Mở file cấu hình /etc/my.cnf.d/server.cnf

Thêm tùy chọn port vào phần cấu hình [mysqld]

...
[mysqld] 
port = 3307
...
  1. Khởi động lại MariaDB/MySQL

Lưu ý:

gcom;// trong Galera sử dụng các port 4567, 4444, 4568 để giao tiếp chứ không sử dụng 3306

Tham khảo: https://www.tecmint.com/change-default-mysql-mariadb-port-in-linux/

Cách RESET password root trong MariaDB/MySQL

  1. Stop dịch vụ MySQL
# SystemD
systemctl stop mariadb
# SysVinit
/etc/init.d/mysqld stop

Kiểm tra lại các tiến trình mysql đã kết thúc hay chưa? Nếu chưa sử dụng lệnh sau để ngắt toàn bộ tiến trình của MySQL.

ps -auxf | grep mysqld
killall mysqld
  1. Khởi động dịch vụ với biến môi trường
# SystemD
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
systemctl start mariadb
systemctl status mariadb
# SysVinit
mysqld_safe --skip-grant-tables &
  1. Đăng nhập vào MySQL và thay đổi pass
mysql -u root

Cập nhật password mới:

USE mysql;
UPDATE user SET password=PASSWORD('YourNewPasswordHere') WHERE User='root' AND Host = 'localhost';
FLUSH PRIVILEGES;
  1. Bỏ biến môi trường và khởi động lại dịch vụ:
# SystemD
systemctl stop mariadb
systemctl unset-environment MYSQLD_OPTS
systemctl start mariadb
# SysVinit
/etc/init.d/mysql stop
/etc/init.d/mysql start

Tham khảo: https://www.tecmint.com/reset-mysql-or-mariadb-root-password/

Backup toàn bộ Database trên Server

USER="root"
PASSWORD=""
#OUTPUT="/Users/rabino/DBs"

#rm "$OUTPUTDIR/*gz" > /dev/null 2>&1

# databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;"  -sN | | grep -Ev "information_schema|performance_schema|mysql|test"`

databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
       # gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done

Thay đổi Password cho một USER

SET PASSWORD FOR 'tom'@'localhost' = PASSWORD('foobar');

Tuning một vài thông số cho MariaDB

[mysqld]
# Tunning
port = 8080
innodb_buffer_pool_size = 768M
innodb_log_file_size = 32M
max_connections = 900
skip-name-resolve
thread_cache_size = 16
query_cache_size = 192M
wait_timeout = 60
tmp_table_size = 64M
max_heap_table_size = 64M

# Logging
log_error=/var/log/mysql/mysql_error.log
log-bin=/var/log/mysql/mysql-bin.log
log-bin-index=bin-log.index
max_binlog_size=100M
binlog_format=row

# general_log_file = /var/log/mysql/mysql.log
# general_log = 1

[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log

Thư viện libssl 1.0.0 Ubuntu 20.04

https://packages.debian.org/search?suite=jessie&arch=any&mode=exactfilename&searchon=contents&keywords=libssl.so.1.0.0
http://security.ubuntu.com/ubuntu/pool/main/o/openssl1.0/
hoangdh commented 1 year ago

Đọc binlog để sinh ra SQL: https://github.com/danfengcao/binlog2sql

./binlog2sql/binlog2sql.py --start-file  mysql-bin.189261 --stop-file mysql-bin.189269
hoangdh commented 1 year ago

How to fix “error no 1062” in MySQL servers and clusters

Here at Bobcares, we provide Server Administration and Maintenance services to website owners and web solution providers.

An error we sometimes see in MySQL servers while updating, restoring or replicating databases is: “Error No: 1062” or “Error Code: 1062” or “ERROR 1062 (23000)“

A full error log that we recently saw in a MySQL cluster is:

could not execute Write_rows event on table mydatabasename.atable; Duplicate entry ’174465′ for key ‘PRIMARY’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.000004, end_log_pos 60121977

What is MySQL Error No: 1062?

Simply put, error 1062 is displayed when MySQL finds a DUPLICATE of a row you are trying to insert.

We’ve seen primarily 4 reasons for this error:

The web application has a bug that adds primary key by large increments, and exhausts the field limit.
MySQL cluster replication tries to re-insert a field.
A database dump file contains duplicate rows because of coding error.
MySQL index table has duplicate rows.

In rare cases, this error is shown when the table becomes too big, but let’s not worry about that for now.

How to fix Error No 1062 when your web appilcation is broken

Every database driven application like WordPress, Drupal or OpenCart distinguishes one user or data set from another using something called a “primary field”.

This primary field should be unique for each user, post, etc.

Web apps use a code like this to insert data:

INSERT INTO table ('id','field1','field2','field3') VALUES ('NULL','data1','data2','data3');

Where “id” is the unique primar key, and is set to auto-increment (that is a number inserted will always be greater than the previous one so as to avoid duplicates).

This will work right if the value inserted is “NULL” and database table is set to “auto-increment”.

Some web apps make the mistake of passing the value as

VALUES ('','data1','data2','data3');

where the first field is omitted. This will insert random numbers into the primary field, rapidly increasing the number to the maximum field limit (usually 2147483647 for numbers).

All subsequent queries will again try to over-write the field with “2147483647”, which MySQL interprets as a Duplicate.

Web app error solution

When we see a possible web application code error, the developers at our Website Support Services create a patch to the app file that fixes the database query.

Now, we have the non-sequential primary key table to be fixed.

For that, we create a new column (aka field), set it as auto-increment, and then make it the primary key.

The code looks approximately like this:

alter table table1 drop primary key; alter table table1 add field2 int not null auto_increment primary key;

Once the primary key fields are filled with sequential values, the name of the new field can be changed to the old one, so that all web app queries will remain the same.

Warning : These commands can get very complex, very fast. So, if you are not sure how these commads work, it’s best to get expert assistance.

Click here to talk to our MySQL administrators. We are online 24/7 and can help you within a few minutes.

How to fix MySQL replication Error Code : 1062

Due to quirks in network or synching MySQL is sometimes known to try and write a row when it is already present in the slave.

So, when we see this error in a slave, we try either one of the following depending on many factors such as DB write traffic, time of day etc.

Delete the row – This is the faster and safer way to continue if you know that the row being written is exactly the same as what’s already present.
Skip the row – If you are not sure there’d be a data loss, you can try skipping the row.

How to delete the row

First delete the row using the primary key.

delete from table1 where field1 is key1;

Then stop and start the slave:

stop slave; start slave; select sleep(5);

Once it is done, check the slave status to see if replication is continuing.

show slave status;

If all is well, you’ll see “Seconds_Behind_Master” as a number. If not, your replication is broken and it needs to be fixed.

How to skip the row

For this, you can set the Skip counter to 1.

Here’s how it could look like:

stop slave; set global SQL_SLAVE_SKIP_COUNTER = 1; start slave; select sleep(5);

Then check the slave status to see if replication is continuing.

show slave status;

Again, if all is well, you’ll see “Seconds_Behind_Master” as a number. If not, your replication is broken and it needs to be fixed.

Proceed with caution

Stopping and starting the slave cannot cause any issue unless you havea very busy database. But, the delete statement, skipping and following up with a broken replication requires expert knowledge about MySQL organization and functioning.

If you are not sure how these commands will affect your database, we recommend you talk to a DB administrator.

Click here to consult our MySQL admins. We are online 24/7 and can attend your request within minutes.

How to fix MySQL restore errors

Restore errors usually take the form of:

ERROR 1062 (23000) at line XXXX: Duplicate entry ‘XXXXXX’ for key X”

When restoring database dumps, this error can happen due to 2 reasons:

The SQL dump file has dulpicate entries.
The index file is duplicate rows.

To find out what is exactly going wrong, we look at the conflicting rows and see if they have the same or different data.

If it’s the same data, then the issue could be due to duplicate index rows. If it is different data, the SQL dump file needs to be fixed.

How to fix duplicate entries in database dumps

This situation can happen when two or more tables are dumped into a single file without checking for duplicates.

To resolve this, one way we’ve used is to create a new primary key field with auto-increment and then change the queries to insert NULL value into it.

Then go ahead with the dump.

Once the new primary field table is fully populated, the name of the field is changed to the old primary table name to preserve the old queries.

The alter table command will look like this:

alter table table1 change column 'newprimary' 'oldprimary' varchar(255) not null;

If your index file is corrupted

There’s no easy way to fix an index file if there are duplicate entries in it.

You’ll have to delete the index file, and restore that file either from backups or from another server where your database dump is restored to a fresh DB server.

The steps involved are quite complex to list out here. We recommend that you consult a DB expert if you suspect the index file is corrupted.

Click here to talk to our MySQL administrators. We are online 24/7 and can help you within a few minutes.

Summary

MySQL error no 1062 can occur due to buggy web applications, corrupted dump files or replication issues. Today we’ve seen the various ways in which the cause of this error can be detected, and how it can be resolved.

hoangdh commented 1 year ago

Mysql replication error 1594 - Relay log read failure - Could not parse relay log event entry

Sometimes mysql replication crash with corrupted relay binlogs and it is not possible to restart it with simple "start slave" command. Here is step by step manual how to fix it.

To check the current slave status execute command:

show slave status\G

You should see result similar to this:

************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.194.74
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001274
          Read_Master_Log_Pos: 1045327404
               Relay_Log_File: 3_dbbackup.003821
                Relay_Log_Pos: 617884398
        Relay_Master_Log_File: mysql-bin.001273
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 617884110
              Relay_Log_Space: 3192816253
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 13
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative

Important values you should notice are Relay_Master_Log_File and Exec_Master_Log_Pos. You will need them to correctly restart replication on your slave.

To restart replication execute following commands:

STOP SLAVE;

RESET SLAVE;

CHANGE MASTER TO master_log_file='mysql-bin.001273', master_log_pos=617884110;

START SLAVE;

To check if replication is working again, execute again command:

show slave status\G

Before you will call your slave as synced, check value of parameter Seconds_Behind_Master from the status command. In our case I have seen value (5187 seconds):

Seconds_Behind_Master: 5187

Within next few minutes was replication synced again with master and replication lag was 0s

Seconds_Behind_Master: 0

This is the time, when we can start using mysql slave again in the production.

hoangdh commented 7 months ago

MySQL table to JSON:

table="social.brands"; mysql -sNe "SELECT JSON_OBJECT($(mysql -sNe "describe ${table}" | grep -v 'auto_increment' | awk {'print $1'} | while read l; do echo -n "'${l}', ${l}, "; done | rev | cut -c 3- | rev)) from ${table}" | jq --slurp .

Ref: https://stackoverflow.com/a/64187921

hoangdh commented 6 months ago 
IPDB=""
PORT="3306"
DB_USER="root"
DB_PASS=""
DB_NAME=""
BACKUP_DIR="/opt/backup"
CLOUD_DIR=""

TODAY=$(date +%F)
OLD_DATA=$(date +%F --date="3 days ago")
DATA_DIR="${BACKUP_DIR}/${TODAY}"
mkdir -vp ${DATA_DIR}
rm -rfv ${DATA_DIR}/*

mydumper --host ${IPDB} --port ${PORT} --user ${DB_USER} --password ${DB_PASS} -B ${DB_NAME} --compress --no-locks --triggers --events --routines --threads 5 --outputdir ${DATA_DIR}

rclone sync --progress "${DATA_DIR}" "${CLOUD_DIR}/${IPDB}/${TODAY}"

rclone delete "${CLOUD_DIR}/${IPDB}/${OLD_DATA}"

rclone check "${DATA_DIR}" "${CLOUD_DIR}/${IPDB}/${TODAY}"

if [ $? -eq 0 ]
then
      message="$(date '+%F %H:%M:%S') - Backup DB ${DB_NAME} success! Size: $(du -sh ${DATA_DIR} | awk {print $1})"
else
      message="$(date '+%F %H:%M:%S') - Backup DB ${DB_NAME} fail!"
fi

TELEGRAM_BOT_TOKEN=""
CHATID="123"

URL="https://api.telegram.org/bot$TELEGRAM_BOT_TOKEN/sendMessage"
curl -s -X POST $URL -d chat_id=${CHATID} -d text="${message}"
hoangdh commented 4 months ago

MySQL Error 1062

Run SQL:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

Or set in mysql config:

[mysqld]
...
slave-skip-errors       = 1062,1032

Ref: https://dba.stackexchange.com/a/65700