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 database, run this command:

use dbispconfig;

And execute the SQL command:

UPDATE sys_user SET passwort = md5('admin') WHERE username = 'admin';

Finally close the mysql shell:

quit;

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 User='root';

(Replace "mynewpassword" with the new root password in the above command).

Then logout from the mysql prompt by typing:

exit

4) Now bring back the running mysql instance into the foreground by typing:

fg

and then press [ctrl] + c to kill the mysql process.

5) Start the mysql server again:

/etc/init.d/mysql start

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.

1. Stop mysql server.
2. Once mysql server is stopped, run a repair on all of *.MYI files via myisamchk:
   
   # myisamchk -r /var/lib/mysql/*/*.MYI
3. Bring up the mysql server.
4. Run a mysqlcheck of all databases via:
   
   # mysqlcheck -c --all-databases | tee /tmp/dbcheck.log
5. 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 TABLE \1;/' >/tmp/dbrepair.sql
6. The file output should be something like:
   
   REPAIR TABLE database1.table1;
REPAIR TABLE database1.table2;
REPAIR TABLE database2.table1;
7. Log into mysql and source the repair script:
   
   # mysql> source /tmp/dbrepair.sql
8. That should run and repair all of the corrupted tables. Verify by running another check and maybe an extended one.
   
   # mysqlcheck -c -e --all-databases

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 version: 10.1.22-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 roundcube
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
MariaDB [roundcube]> OPTIMIZE TABLE cache;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| roundcube.cache | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| roundcube.cache | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.04 sec)

MariaDB [roundcube]> quit
Bye
root@web [~]#

If you want to run the command for multiple tables from the same database, use:

OPTIMIZE TABLE table1,table2,table3;

OPTIMIZE TABLE works with InnoDB, MyISAM, and ARCHIVE tables.

2. mysqlcheck command

The mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables.

To check one table use: mysqlcheck db_name tbl_name
To check all tables from a database: mysqlcheck –databases db_name
To check the tables from all the databases on the server: mysqlcheck –all-databases

Notice that database tables are locked while mysqlcheck is running. No records can be inserted or deleted from the tables.

root@web [~]# mysqlcheck roundcube
roundcube.cache                                    OK
roundcube.cache_index                              OK
roundcube.cache_messages                           OK
roundcube.cache_shared                             OK
roundcube.cache_thread                             OK
roundcube.contactgroupmembers                      OK
roundcube.contactgroups                            OK
roundcube.contacts                                 OK
roundcube.cp_schema_version                        OK
roundcube.dictionary                               OK
roundcube.identities                               OK
roundcube.searches                                 OK
roundcube.session                                  OK
roundcube.system                                   OK
roundcube.users                                    OK
root@web [~]# 

To optimize a database, use:

root@web [~]# mysqlcheck -o roundcube
roundcube.cache
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_index
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_messages
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_shared
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_thread
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contactgroupmembers
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contactgroups
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contacts
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cp_schema_version                        Table is already up to date
roundcube.dictionary
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.identities
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.searches
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.session
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.system
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.users
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
root@web [~]#

To optimize all the database on the sever use:

root@web [~]# mysqlcheck -o -A

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 virtual
Enter password:
virtual.accounts                              OK

Now, what if we just want to check one table of the database?

mysqlcheck -p -u root virtual trivia
Enter password:
virtual.accounts                              OK

As you can see, all you have to do to check just one table is to specify the table name after you specify the database name.  Now, what if you have a database with more than two tables, and you want to check more than one, but not all of the tables?  That’s easy.  Just specify all of the tables that you want to check after you specify the database.

mysqlcheck -p -u root mysql db host proc
Enter password:
mysql.db                                           OK
mysql.host                                         OK
mysql.proc                                         OK

You can also check more than one database at a time.  Let’s say that you want to check the “payroll” and the “contact” databases.

mysqlcheck -p -u root –databases payroll contact
Enter password:
payroll.last_name                                   OK
payroll.first_name                                  OK
payroll.SSN                                         OK
payroll.pay_rate                                    OK
contact.last_name                                   OK
contact.first_name                                  OK
contact.phone_number                                OK

This time, by adding the “–databases” switch, all names that you enter on the command-line will be treated as database names.

It’s also a simple matter to check all databases at once, just by using the “–all-databases” switch:

mysqlcheck -p -u root –all-databases
Enter password:
contacts.names                                     OK
contacts.phone_numbers                             OK
contacts.trivia                                    OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.func                                         OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

You can also use mysqlcheck to perform corrective maintenance.  There’s only one catch, though.  MySQL databases can use two different types of tables–either MyISAM tables or InnoDB tables.  While mysqlcheck can perform checks on either type of table, it can only repair MyISAM tables.

mysqlcheck -p -u root –repair virtual
Enter password:
virtual.accounts                              OK

You can do more extensive repairs by adding another switch:

mysqlcheck -p -u root –repair –extended virtual
Enter password:
virtual.accounts                              OK

Or, you can also do a quick repair:

mysqlcheck -p -u root –repair –quick virtual
Enter password:
virtual.accounts                              OK

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.

mysqlcheck utility commands in MySQL

mysqlcheck

mysqlcheck --help
mysqlcheck --version

Usage: mysqlcheck [OPTIONS] database [tables]
OR     mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
OR     mysqlcheck [OPTIONS] --all-databases

mysqlcheck --login-path=dba -c testdb
mysqlcheck --login-path=root -r testdb emp
mysqlcheck --login-path=dba -o devdb dept

mysqlcheck --login-path=mydba --check --all-databases --medium-check --auto-repair --compress --force --medium-check --skip-write-binlog --debug-info
mysqlcheck --login-path=local --analyze --databases mydb --compress --skip-write-binlog --debug-info

mysqlcheck --login-path=dba --check --databases devdb
mysqlcheck --login-path=dba --analyze --databases proddb
mysqlcheck --login-path=dba --repair --databases testdb
mysqlcheck --host=linux001 --port=3308 --user=root --password -c proddb

This program can be used to CHECK (-c, -m, -C), REPAIR (-r), ANALYZE (-a), or OPTIMIZE (-o) tables. Some of the options (like -e or -q) can be used at the same time. Not all options are supported by all storage engines.

Please check MySQL manual for latest information about the above. 
The options -c, -r, -a, and -o are exclusive to each other, which means that the last option will be used, if several was specified.

The option -c will be used by default, if none was specified.
You can change the default behavior by making a symbolic link, or copying this file somewhere with another name, the alternatives are:
mysqlrepair:   The default option will be -r
mysqlanalyze:  The default option will be -a
mysqloptimize: The default option will be -o

Default options of mysql check, are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysqlcheck client

The following mysqlcheck options may be given as the first argument:
--print-defaults         Print the program argument list and exit.
--no-defaults            Don't read default options from any option file,  except for login file.
--defaults-file=#        Only read default options from the given file #.
--defaults-extra-file=#  Read this file after the global files are read.
--defaults-group-suffix=#  Also read groups with concat(group, suffix)
--login-path=#           Read this path from the login file.

  -A, --all-databases  Check all the databases. This is the same as --databases with all databases selected.
  -a, --analyze        Analyze given tables.
  -1, --all-in-1       Instead of issuing one query for each table, use one query per database, naming all tables in the database in a comma-separated list.
  --auto-repair        If a checked table is corrupted, automatically fix it.  Repairing will be done after all tables have been checked, if corrupted ones were found.
  --bind-address=name  IP address to bind to.
  --character-sets-dir=name  Directory for character set files.
  -c, --check          Check table for errors.
  -C, --check-only-changed      Check only tables that have changed since last check or  haven't been closed properly.
  -g, --check-upgrade  Check tables for version-dependent changes. May be used  with --auto-repair to correct tables requiring version-dependent updates.
  --compress           Use compression in server/client protocol.
  -B, --databases      Check several databases. Note the difference in usage; in this case no tables are given. All name arguments are  regarded as database names.

  -#, --debug[=#]      This is a non-debug version. Catch this and exit.
  --debug-check        This is a non-debug version. Catch this and exit.
  --debug-info         This is a non-debug version. Catch this and exit.
  --default-character-set=name      Set the default character set.
  --default-auth=name  Default authentication client-side plugin to use.
  --enable-cleartext-plugin     Enable/disable the clear text authentication plugin.
  -F, --fast           Check only tables that haven't been closed properly.
  --fix-db-names       Fix database names.
  --fix-table-names    Fix table names.
  -f, --force          Continue even if we get an SQL error.
  -e, --extended       If you are using this option with CHECK TABLE, it will ensure that the table is 100 percent consistent, but will take a long time. If you are using this option with REPAIR TABLE, it will force using old slow repair with keycache method, instead of much faster repair by  sorting.

  -?, --help           Display this help message and exit.
  -h, --host=name      Connect to host.
  -m, --medium-check   Faster than extended-check, but only finds 99.99 percent of all errors. Should be good enough for most cases.
  --write-binlog       Log ANALYZE, OPTIMIZE and REPAIR TABLE commands. Use --skip-write-binlog when commands should not be sent to replication slaves. (Defaults to on; use --skip-write-binlog to disable.)
  --secure-auth        Refuse client connecting to server if it uses old (pre-4.1.1) protocol. Deprecated. Always TRUE
  -o, --optimize       Optimize table.

  -p, --password[=name]  Password to use when connecting to server. If password is not given, it's solicited on the tty.
  --plugin-dir=name    Directory for client-side plugins.
  -P, --port=#         Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).
  --protocol=name      The protocol to use for connection (tcp, socket, pipe, memory).
  -q, --quick            If you are using this option with CHECK TABLE, it prevents the check from scanning the rows to check for  wrong links. This is the fastest check. If you are using this option with REPAIR TABLE, it will try to repair only the index tree. This is the fastest repair method for a table.
  -r, --repair         Can fix almost anything except unique keys that aren't unique.
  -s, --silent         Print only error messages.
  --skip-database=name  Don't process the database specified as argument
  -S, --socket=name    The socket file to use for connection.

  --ssl-mode=name      SSL connection mode.
  --ssl                Deprecated. Use --ssl-mode instead.  (Defaults to on; use --skip-ssl to disable.)
  --ssl-verify-server-cert   Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
  --ssl-ca=name        CA file in PEM format.
  --ssl-capath=name    CA directory.
  --ssl-cert=name      X509 cert in PEM format.
  --ssl-cipher=name    SSL cipher to use.
  --ssl-key=name       X509 key in PEM format.
  --ssl-crl=name       Certificate revocation list.
  --ssl-crlpath=name   Certificate revocation list path.

  --tls-version=name   TLS version to use, permitted values are: TLSv1, TLSv1.1, TLSv1.2
  --tables               Overrides option --databases (-B).
  --use-frm            When used with REPAIR, get table structure from .frm file, so the table can be repaired even if .MYI header is corrupted.
  -u, --user=name      User for login if not current user.
  -v, --verbose        Print info about the various stages.
  -V, --version        Output version information and exit.

mysqlcheck variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
all-databases                     FALSE
all-in-1                              FALSE
auto-repair                        FALSE
bind-address                      (No default value)
character-sets-dir               (No default value)
compress                           FALSE
databases                          FALSE
default-character-set          utf8
default-auth                       (No default value)
enable-cleartext-plugin       FALSE
fast                                   FALSE
fix-db-names                      FALSE
fix-table-names                  FALSE
force                                 FALSE
extended                           FALSE
host                                  (No default value)
write-binlog                       TRUE
secure-auth                       TRUE
plugin-dir                          (No default value)
port                                  3306
quick                                FALSE
silent                               FALSE
skip-database
socket                              /data/mysql/mysql.sock
ssl                                   TRUE
ssl-verify-server-cert         FALSE
ssl-ca                              (No default value)
ssl-capath                        (No default value)
ssl-cert                            (No default value)
ssl-cipher                         (No default value)
ssl-key                            (No default value)
ssl-crl                              (No default value)
ssl-crlpath                        (No default value)
tls-version                        (No default value)
use-frm                            FALSE
user                                (No default value)