Mysql - How to reset the administrator password in ISPConfig 3

If you lost your ISPConfig 3 administrator password, you can reset it with the following SQL query. UPDATE sys_user SET passwort = md5('admin') WHERE username = 'admin'; The SQL query sets the password to "admin" for the user "admin", it has to be executed in the ISPConfig mysql database, e.g. with phpmyadmin. If you dont have phpmyadmin installed, then the query can be executed with the mysql commandline utility as well: Login to the mysql database. mysql -u root -p Then enter the password of the mysql root user. To switch to the ISPConfig...

How to reset the MySQL root password

The following steps describe the procedure to reset the mysql root password on Linux. 1) Stop the mysql server /etc/init.d/mysql stop 2) Start the mysql server manually without permission tables which allows us to login as root user without password: mysqld_safe --skip-grant-tables & 3) Login into mysql as root user without a password and switch to the "mysql" database: mysql -u root mysql Then execute this SQL query to set a new password for the mysql root user: use mysql;update user set Password=PASSWORD('mynewpassword') WHERE...

How to enable the query log in MySQL

To debug applications which use a mysql database, it comes in handy to enable the query log to get all SQL queries that were sent to the database.  Open the MySQL configuration file (my.cnf) vi /etc/mysql/my.cnf and add the line: log=/var/log/mysql.log in the [mysql] section of the file. Depending on the Linux distribution that you use, the my.cnf file can be /etc/my.cnf or /etc/mysql/my.cnf. Afterwards restart mysql to apply the new the configuration: /etc/init.d/mysql restart ...

mysqlcheck - mysql repair tables

With a recent OS upgrade, some of the mysql database tables got corrupted. Below is how I was able to get it repaired. Stop mysql server. Once mysql server is stopped, run a repair on all of *.MYI files via myisamchk: # myisamchk -r /var/lib/mysql/*/*.MYI Bring up the mysql server. Run a mysqlcheck of all databases via: # mysqlcheck -c --all-databases | tee /tmp/dbcheck.log Grep for "error" on the log and proceed to create a sql file to be run to repair the tables. # grep error -B1 /tmp/dbcheck.log | grep -v "error\|--" | sed 's/\(.*\)/REPAIR...

How to optimize MySQL/MariaDB tables

It’s a good idea to perform database maintenance from time to time. One thing is to do is to optimize the tables. We have two options: 1. OPTIMIZE TABLE command Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table. See below how to use it. root@web [~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3670 Server...

Maintaining MySQL Databases

As a MySQL administrator, you’ll probably end up doing some preventive and corrective database maintenance.  You can use mysqlcheck for both. First, consider the “virtual” database.  It has  one table called ‘accounts”.  You can check the whole database with the following command:mysqlcheck -p -u root virtualEnter password:virtual.accounts                              OK Now, what...

How To Check And Repair All MySQL Databases on Debian And Ubuntu Linux

The Following command can be used to check and repair all MySQL databases on a Ubuntu or Debian Linux System. Ubuntu Linux sudo mysqlcheck --defaults-file=/etc/mysql/debian.cnf --auto-repair --optimize --all-databases Debian Linux mysqlcheck --defaults-file=/etc/mysql/debian.cnf --auto-repair --optimize --all-databases The benefit of the above command is that it uses the debian-sys-maint login to MySQL which is available on every Debian and Ubuntu System, so you don't have to provide the MySQL root login details...