To check and repair all databases in a database run mysqlcheck -A –auto-repair -u root -p Forcefully optimize all tables, automatically fixing table errors that may come up. mysqlcheck -A –auto-repair -f -o -u root -p To check all databases mysqlcheck –all-databases -u root -p -c To analyze all tables in all databases: mysqlcheck –all-databases … Read more
When restoring a MySQL 8 database backup on a MariaDB server, I get the following error To fix this error, open the file in a text editor, find utf8mb4_0900_ai_ci, and replace it with utf8mb4_general_ci. I used the following sed command to do the replacement. See MySQL
Amazon Linux 2 come with MariaDB by default. To install MySQL 5.7, install repository with sudo rpm -Uvh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm Install MySQL 5.7 with sudo yum install mysql-community-server Set MySQL to start on boot sudo systemctl enable mysqld Start MySQL server sudo systemctl start mysqld By default MySQL 5.7 installation generate a temporary password. To find … Read more
When i try to create MySQL user on a server, i get following error mysql> GRANT ALL PRIVILEGES ON *.* TO ‘centovacast’@’localhost’ IDENTIFIED BY ‘*5B5F6EB22D64C7D8FE384BEF890B55964482A144’ WITH GRANT OPTION; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> This is due to MySQL validate password settings mysql> SHOW VARIABLES LIKE ‘validate_password%’; +————————————–+——–+ … Read more
To change the password for a MySQL user, run mysqladmin -u user_name_here -p password new_password_here Or via SQL UPDATE mysql.user SET Password=PASSWORD(‘NEW_PASSWORD_HERE’) WHERE User=’USER_NAME_HERE’; FLUSH PRIVILEGES; See MySQL
To reset MySQL root password on Bitnami server, first check MySQL server version you are running. mysql –version Create a file vi /tmp/mysql-init Add following text For MySQL 5.7 or MySQL 8 ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NEW_PASSWORD’; ALTER USER ‘root’@’127.0.0.1’ IDENTIFIED BY ‘NEW_PASSWORD’; For MySQL 5.6 UPDATE mysql.user SET Password=PASSWORD(‘NEW_PASSWORD’) WHERE User=’root’; FLUSH PRIVILEGES; … Read more
When using systemctl, you can set value for open_files_limit in my.cnf file. Default installation of MariaDB 10 have open_files_limit set to 16384. To increase value for open_files_limit, create file mkdir /etc/systemd/system/mariadb.service.d/ vi /etc/systemd/system/mariadb.service.d/limitnofile.conf Add [Service] LimitNOFILE=1048576 Reload systemctl systemctl daemon-reload Restart mariadb systemctl restart mariadb After doing this, it get changed to 32184 instead of … Read more
When i try to repair a crashed database table, i get error mysql> repair table visitorstats_sessions; +————————————-+——–+———-+————————————————————————+ | Table | Op | Msg_type | Msg_text | +————————————-+——–+———-+————————————————————————+ | centovacastdb.visitorstats_sessions | repair | error | Can’t create new tempfile: ‘./centovacastdb/visitorstats_sessions.TMD’ | | centovacastdb.visitorstats_sessions | repair | status | Operation failed | +————————————-+——–+———-+————————————————————————+ 2 rows in set … Read more
I had to restore a large MySQL backup file. When restoring one of the table resulted in error. To debug the error, i wanted to split the MySQL backup taken using mysqldump into tables. You can use csplit command to do this csplit -s -ftable MYSQLDUMP_BACKUP_FILE_HERE “/– Table structure for table/” {*} This will generate … Read more
This bash script is used to auto-restart MySQL or MariaDB database if it crashes or stops for any reason. Create file mkdir /usr/serverok vi /usr/serverok/mysql_monitor.sh Add #!/bin/bash # Author: ServerOK # Web: https://serverok.in/mysql-restart-bash MYSQL_REPLY=”$(mysqladmin ping)” TIME_STAMP=”$(date “+%Y-%m-%d %H:%M:%S”)” if [[ ! “$MYSQL_REPLY” =~ “mysqld is alive” ]] then systemctl restart mariadb echo -e “${TIME_STAMP} MySQL … Read more
To find the size of databases using SQL command, run the following SQL in MySQL prompt. Example FInd disk usage by tables To find disk usage by tables in database, run In the above SQL, replace DB_NAME_HERE with the actual name of the database. Example See MySQL
