mysql

Reset MySQL root password on Bitnami server

by
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

MariaDB Change Open Files Limit

by

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

MySQL Can’t create new tempfile

by

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

Split mysqldump backup file into tables

by
MySQL

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

Auto Restart MySQL if Crashed

by
MySQL

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

Find MySQL Database and Table Size

by

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

Percona Monitoring and Management

by

Percona Monitoring and Management is an Open Source monitoring software for MySQL, PostgreSQL and MongoDB. https://www.percona.com/software/database-tools/percona-monitoring-and-management It is based on grafana and node_exporter. You can see source code at https://github.com/percona/pmm Install instructions for Percona Monitoring and Management available at https://www.percona.com/software/pmm/quickstart See MySQL

MySQL ERROR Unable to create or change a table without a primary key

by

When trying to restore a database backup to Managed DigitialOcean MySQL 8 database, i get following error root@ocp:~# mysql -u doadmin -p’BKwsQcqEGbSV3w’ -h db-sevrerok-do-user-8606188-0.b.db.ondigitalocean.com -P 25060 serverok_db < serverok_db.sql mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 3750 (HY000) at line 223: Unable to create or change a table ... Read more

MariaDB ERROR 1524 Plugin unix_socket is not loaded

by

After upgrading MariaDB on Ubuntu server, i got following error root@server:~# mysql ERROR 1524 (HY000): Plugin ‘unix_socket’ is not loaded root@server:~# Users created worked fine. Only root user had this error. To fix, you need to enable auth_socket.so plugin. Edit file vi /etc/mysql/mariadb.conf.d/50-server.cnf Find [mysqld] Add below plugin-load-add = auth_socket.so Resatrt MariaDB systemctl restart mysqld … Read more