Welcome to Centmin Mod Community
Register Now

MariaDB Insight Guide How to use mysql_install_db to reset your MySQL database directory

Discussion in 'Centmin Mod Insights' started by eva2000, Jan 10, 2017.

Thread Status:
Not open for further replies.
  1. eva2000

    eva2000 Administrator Staff Member

    53,811
    12,159
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,711
    Local Time:
    4:58 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Centmin Mod is provided as is so troubleshooting MariaDB MySQL database and server issues is left to the end user deal with. However, at times you may need to totally reset and reload all your MySQL database data - usually the case if it involves MySQL InnoDB tables. If you have problems or issues with below steps, please start a new thread prefixed with MariaDB in forum here.

    When MySQL InnoDB database and tables have issues, sometimes only way to fix it is to use mysqldump command to fully backup all MySQL databases and system database (for db name = mysql) and wipe the MySQL data directory at /var/lib/mysql and reinstall system mysql database and /var/lib/mysql directory via mysql_install_db command and then restore your database backups. Below steps do just this.

    Below steps can permanently delete all MySQL data if not properly backed up



    Ensure you have enough disk free space at /home to backup all databases in a single sql backup file.
    Code (Text):
    df -hT
    

    Below steps assume MariaDB MySQL server is started and running. If your MariaDB MySQL server can not start, you may need to do innodb force recover outlined at MySQL :: MySQL 5.6 Reference Manual :: 14.21.2 Forcing InnoDB Recovery. In your /etc/my.cnf under the [mysqld] group add the line innodb_force_recovery = 1
    Code (Text):
    [mysqld]
    innodb_force_recovery = 1
    

    Then start mysql server if not running
    Code (Text):
    mysqlstart


    Note: remove the line innodb_force_recovery = 1 from /etc/my.cnf AFTER you have restored all your databases in below restore step and restart MariaDB MySQL server

    innodb_force_recovery value of 1 means the following forcing MariaDB MySQL server to start even with corruption so that at least you can get a mysqldump backup of your database data first for full restore steps outlined further below.
    If innodb_force_recovery = 1 doesn't work, you may need to try next level up in your /etc/my.cnf under the [mysqld] group change the line to innodb_force_recovery = 2
    Code (Text):
    [mysqld]
    innodb_force_recovery = 2
    

    Then start mysql server if not running
    Code (Text):
    mysqlstart


    Note: remove the line innodb_force_recovery = 2 from /etc/my.cnf AFTER you have restored all your databases in below restore step and restart MariaDB MySQL server

    Backup & Wipe Steps



    With MariaDB MySQL server started, then backup all databases using mysqldump command
    Code (Text):
    mkdir -p /home/mysqlbackup
    mysqldump --all-databases > /home/mysqlbackup/alldatabases.sql
    

    stop mysql server but first flush tables and wait 240 seconds
    Code (Text):
    mysqladmin flush-tables
    sleep 240
    mysqlstop
    

    backup existing mysql data directory /var/lib/mysql to /var/lib/mysql.orig ensuring enough disk free space at /var to house it
    Code (Text):
    cp -a /var/lib/mysql/ /var/lib/mysql.orig
    

    delete and wipe all mysql data from datadir for mysql server at /var/lib/mysql (ensure backups you have done above are working and complete as this step will wipe all data for restore from backups from sql files)
    Code (Text):
    cd /var/lib/mysql/
    rm -rf /var/lib/mysql
    

    Use mysql_install_db command to re-create /var/lib/mysql mysql data directory from scratch
    Code (Text):
    mysql_install_db --user=mysql
    

    example output
    Code (Text):
    mysql_install_db --user=mysql
    Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...
    2017-01-10  1:07:29 139877245270272 [Note] /usr/sbin/mysqld (mysqld 10.1.20-MariaDB) starting as process 3230 ...
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: The InnoDB memory heap is disabled
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: Compressed tables use zlib 1.2.7
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: Using Linux native AIO
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: Using SSE crc32 instructions
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: Initializing buffer pool, size = 48.0M
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: Completed initialization of buffer pool
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: Setting file ./ibdata1 size to 10 MB
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: Database physically writes the file full: wait...
    2017-01-10  1:07:29 139877245270272 [Note] InnoDB: Setting log file ./ib_logfile101 size to 128 MB
    InnoDB: Progress in MB: 100
    2017-01-10  1:07:30 139877245270272 [Note] InnoDB: Setting log file ./ib_logfile1 size to 128 MB
    InnoDB: Progress in MB: 100
    2017-01-10  1:07:30 139877245270272 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
    2017-01-10  1:07:30 139877245270272 [Warning] InnoDB: New log files created, LSN=45883
    2017-01-10  1:07:30 139877245270272 [Note] InnoDB: Doublewrite buffer not found: creating new
    2017-01-10  1:07:30 139877245270272 [Note] InnoDB: Doublewrite buffer created
    2017-01-10  1:07:31 139877245270272 [Note] InnoDB: 128 rollback segment(s) are active.
    2017-01-10  1:07:31 139877245270272 [Warning] InnoDB: Creating foreign key constraint system tables.
    2017-01-10  1:07:31 139877245270272 [Note] InnoDB: Foreign key constraint system tables created
    2017-01-10  1:07:31 139877245270272 [Note] InnoDB: Creating tablespace and datafile system tables.
    2017-01-10  1:07:31 139877245270272 [Note] InnoDB: Tablespace and datafile system tables created.
    2017-01-10  1:07:31 139877245270272 [Note] InnoDB: Creating zip_dict and zip_dict_cols system tables.
    2017-01-10  1:07:31 139877245270272 [Note] InnoDB: zip_dict and zip_dict_cols system tables created.
    2017-01-10  1:07:31 139877245270272 [Note] InnoDB: Waiting for purge to start
    2017-01-10  1:07:31 139877245270272 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.34-79.1 started; log sequence number 0
    2017-01-10  1:07:31 139876775229184 [Note] InnoDB: Dumping buffer pool(s) not yet started
    OK
    Filling help tables...
    2017-01-10  1:07:33 139900069169408 [Note] /usr/sbin/mysqld (mysqld 10.1.20-MariaDB) starting as process 3257 ...
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB: The InnoDB memory heap is disabled
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB: Compressed tables use zlib 1.2.7
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB: Using Linux native AIO
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB: Using SSE crc32 instructions
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB: Initializing buffer pool, size = 48.0M
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB: Completed initialization of buffer pool
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB: Highest supported file format is Barracuda.
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB: 128 rollback segment(s) are active.
    2017-01-10  1:07:34 139900069169408 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.34-79.1 started; log sequence number 1622825
    2017-01-10  1:07:34 139899600606976 [Note] InnoDB: Dumping buffer pool(s) not yet started
    OK
    Creating OpenGIS required SP-s...
    2017-01-10  1:07:36 140071091333376 [Note] /usr/sbin/mysqld (mysqld 10.1.20-MariaDB) starting as process 3284 ...
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: The InnoDB memory heap is disabled
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: Compressed tables use zlib 1.2.7
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: Using Linux native AIO
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: Using SSE crc32 instructions
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: Initializing buffer pool, size = 48.0M
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: Completed initialization of buffer pool
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: Highest supported file format is Barracuda.
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: 128 rollback segment(s) are active.
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB: Waiting for purge to start
    2017-01-10  1:07:37 140071091333376 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.34-79.1 started; log sequence number 1622835
    2017-01-10  1:07:37 140070623352576 [Note] InnoDB: Dumping buffer pool(s) not yet started
    OK
    
    To start mysqld at boot time you have to copy
    support-files/mysql.server to the right place for your system
    
    PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
    To do so, start the server, then issue the following commands:
    
    '/usr/bin/mysqladmin' -u root password 'new-password'
    '/usr/bin/mysqladmin' -u root -h centos7.localdomain password 'new-password'
    
    Alternatively you can run:
    '/usr/bin/mysql_secure_installation'
    
    which will also give you the option of removing the test
    databases and anonymous user created by default.  This is
    strongly recommended for production servers.
    
    See the MariaDB Knowledgebase at http://mariadb.com/kb or the
    MySQL manual for more instructions.
    
    You can start the MariaDB daemon with:
    cd '/usr' ; /usr/bin/mysqld_safe --datadir='/var/lib/mysql'
    
    You can test the MariaDB daemon with mysql-test-run.pl
    cd '/usr/mysql-test' ; perl mysql-test-run.pl
    
    Please report any problems at http://mariadb.org/jira
    
    The latest information about MariaDB is available at http://mariadb.org/.
    You can find additional information about the MySQL part at:
    http://dev.mysql.com
    Consider joining MariaDB's strong and vibrant community:
    https://mariadb.org/get-involved/
    

    then chmod /var/lib/mysql directory
    Code (Text):
    chmod 0755 /var/lib/mysql
    

    then start mysql server

    Note: remove the line innodb_force_recovery = 1 if set from /etc/my.cnf before you start MariaDB MySQL server at this stage
    Code (Text):
    mysqlstart
    


    Restore Steps



    temp rename /root/.my.cnf as the root password set in there is not in mysql system database right now as you reset the data
    Code (Text):
    mv /root/.my.cnf /root/disable.my.cnf
    

    then restore all data from sql file backup
    Code (Text):
    mysql < /home/mysqlbackup/alldatabases.sql
    

    then rename back the /root/disable.my.cnf file to /root/.my.cnf
    Code (Text):
    mv /root/disable.my.cnf /root/.my.cnf
    

    then restart mysql server for previous mysql root user privileges to take effect
    Code (Text):
    mysqlrestart
    

    Check server version
    Code (Text):
    mysqladmin ver
    

    example
    Code (Text):
    mysqladmin ver
    mysqladmin  Ver 9.1 Distrib 10.1.20-MariaDB, for Linux on x86_64
    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
    
    Server version          10.1.20-MariaDB
    Protocol version        10
    Connection              Localhost via UNIX socket
    UNIX socket             /var/lib/mysql/mysql.sock
    Uptime:                 2 sec
    
    Threads: 1  Questions: 1  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.500
    

    check mysql error logs

    for CentOS 6
    Code (Text):
    tail -30 /var/log/mysqld.log
    

    for CentOS 7
    Code (Text):
    journalctl -u mariadb --no-pager -n30
    


    example for CentOS 7
    Code (Text):
    journalctl -u mariadb --no-pager -n30
    -- Logs begin at Tue 2017-01-10 01:04:45 UTC, end at Tue 2017-01-10 03:50:35 UTC. --
    Jan 10 01:15:24 centos7.localdomain systemd[1]: Started MariaDB database server.
    Jan 10 03:46:31 centos7.localdomain systemd[1]: Stopping MariaDB database server...
    Jan 10 03:46:31 centos7.localdomain mysqld[3649]: 2017-01-10  3:46:31 140103052331776 [Note] /usr/sbin/mysqld: Normal shutdown
    Jan 10 03:46:31 centos7.localdomain mysqld[3649]: 2017-01-10  3:46:31 140103052331776 [Note] Event Scheduler: Purging the queue. 0 events
    Jan 10 03:46:31 centos7.localdomain mysqld[3649]: 2017-01-10  3:46:31 140102575552256 [Note] InnoDB: FTS optimize thread exiting.
    Jan 10 03:46:31 centos7.localdomain mysqld[3649]: 2017-01-10  3:46:31 140103052331776 [Note] InnoDB: Starting shutdown...
    Jan 10 03:46:32 centos7.localdomain mysqld[3649]: 2017-01-10  3:46:32 140103052331776 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
    Jan 10 03:46:33 centos7.localdomain mysqld[3649]: 2017-01-10  3:46:33 140103052331776 [Note] InnoDB: Shutdown completed; log sequence number 1651426
    Jan 10 03:46:33 centos7.localdomain mysqld[3649]: 2017-01-10  3:46:33 140103052331776 [Note] /usr/sbin/mysqld: Shutdown complete
    Jan 10 03:46:33 centos7.localdomain systemd[1]: Starting MariaDB database server...
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] /usr/sbin/mysqld (mysqld 10.1.20-MariaDB) starting as process 16103 ...
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: The InnoDB memory heap is disabled
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: Compressed tables use zlib 1.2.7
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: Using Linux native AIO
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: Using SSE crc32 instructions
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: Initializing buffer pool, size = 48.0M
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: Completed initialization of buffer pool
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: Highest supported file format is Barracuda.
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: 128 rollback segment(s) are active.
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB: Waiting for purge to start
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.34-79.1 started; log sequence number 1651426
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139855975675648 [Note] InnoDB: Dumping buffer pool(s) not yet started
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] Plugin 'FEEDBACK' is disabled.
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] Server socket created on IP: '::'.
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: 2017-01-10  3:46:34 139856461809920 [Note] /usr/sbin/mysqld: ready for connections.
    Jan 10 03:46:34 centos7.localdomain mysqld[16103]: Version: '10.1.20-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
    Jan 10 03:46:34 centos7.localdomain systemd[1]: Started MariaDB database server.
    


    Note: remove the line innodb_force_recovery = 1 from /etc/my.cnf after you have restored all your databases and restart MariaDB MySQL server

    Troubleshooting




    If for whatever reason, you didn't properly backup and restore the database named = mysql, then mysql root user and any mysql user/pass would not have been restored so may not be able to connect with your web apps for mysql users. The database named = mysql contains all your mysql user/pass details including root user details. In such case you will need to reset mysql root user password.

    You can try resetting MySQL root password

    Then you would need to recreate your web apps' mysql user/pass and assign them to their respective mysql database names. You can use Centmin Mod Addon at /usr/local/src/centminmod/addons/mysqladmin_shell.sh for this as outlined at mysqladmin_shell.sh Shell based Addon

    options available
    Code (Text):
    ./mysqladmin_shell.sh
    
    ./mysqladmin_shell.sh {multidb|setglobaluser|setuserdb|setpass|deluser|showgrants}
    
    multidb - multiple mysql databse/user creation mode passing a file name containing db, user, pass 3 column entries
    setglobaluser - create a mysql username with access to all databases on server without SUPER ADMIN privileges (non-root)
    setuserdb - create individual mysql username and databases or assign a new database to an existing mysql username
    setpass - change mysql username password
    deluser - delete a mysql usernames
    showgrants - show existing mysql username granted privileges
    

    and use setglobaluser option to create a single mysql username to access all your databases if you want
    Code (Text):
    ./mysqladmin_shell.sh setglobaluser
    

    Then update your web app's mysql user and pass to the one created by setglobaluser option

    or if you want individual mysql user/pass again, use setuserdb option but specify the mysql user/pass you want but instead when prompted to create a database, just specify a dummy database name i.e. dummydb1, dummydb2 etc
    Code (Text):
    ./mysqladmin_shell.sh setuserdb
    

    then that creates the mysql user/pass then rerun setuserdb but instead of create user/pass select option to assign a new database to existing mysql username/password and specify your actual database names for your web apps
    Code (Text):
    ./mysqladmin_shell.sh setuserdb
    
     
    Last edited: Jan 11, 2017
Thread Status:
Not open for further replies.