MYSQL database

Database backup & Restore:
Database backup can be taken by using mysqldump command. The command
usage syntax is given below. You need to give database user password, when
command is executed and prompt for password.
cmd> mysqldump -u [username] -p [database name] > [database name].sql
Database backup available in the SQL format can restored by using below
command, syntax is undermentioned. (Note: You need to create a new database,
in which you are going to restore the database dump).
cmd> mysql -u [username] -p [new database name] < [database name].sql Example mysqldump -u developmentmant_N915341 -p developmentmant_U82b96a >
How to Check MyISAM Tables for Errors
To check a ​ MyISAM​ table, use the following commands:
● myisamchk ​ tbl_name
● This finds 99.99% of all errors. What it cannot find is corruption that involves ​ only ​ the
data file (which is very unusual). If you want to check a table, you should normally run
myisamchk​ without options or with the ​ -s​ (silent) option.
● myisamchk -m ​ tbl_name
● This finds 99.999% of all errors. It first checks all index entries for errors and then reads
through all rows. It calculates a checksum for all key values in the rows and verifies that
the checksum matches the checksum for the keys in the index tree.
● myisamchk -e ​ tbl_name
● This does a complete and thorough check of all data (​ -e​ means “extended check”). It
does a check-read of every key for each row to verify that they indeed point to the
correct row. This may take a long time for a large table that has many indexes.
Normally, ​ myisamchk​ stops after the first error it finds. If you want to obtain moreinformation, you can add the ​ -v​ (verbose) option. This causes ​ myisamchk​ to keep
going, up through a maximum of 20 errors.
● myisamchk -e -i ​ tbl_name
● This is like the previous command, but the ​ -i​ option tells ​ myisamchk​ to print additional
statistical information.
You can repair a crashed mysql table using PHPMyAdmin. Watch the video in the link to know,
Video: ​
How to Repair MyISAM Tables
The discussion in this section describes how to use ​ myisamchk​ on ​ MyISAM​ tables (extensions ​ .MYI
and ​ .MYD​ ).
You can also use the ​ CHECK TABLE​ and ​ REPAIR TABLE​ statements to check and repair ​ MyISAM
tables. See ​ Section, “CHECK TABLE Syntax”​ , and​ Section, “REPAIR TABLE
Syntax”​ .
Symptoms of corrupted tables include queries that abort unexpectedly and observable errors such
as these:
● tbl_name .
frm​ is locked against change
● Can’t find file ​ tbl_name ​ .MYI​ (Errcode: ​ nnn ​ )
● Unexpected end of file
● Record file is crashed
● Got error ​ nnn ​ from table handlerTo get more information about the error, run ​ perror​ ​ nnn ​ , where ​ nnn ​ is the error number. The
following example shows how to use ​ perror​ to find the meanings for the most common error
numbers that indicate a problem with a table:
shell>​ perror​ 126 127 132 134 135 136 141 144 145
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted ​ ( ​ or record file crashed​ )
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired
Note that error 135 (no more room in record file) and error 136 (no more room in index file) are not
errors that can be fixed by a simple repair. In this case, you must use ​ ALTER TABLE​ to increase the
MAX_ROWS​ and ​ AVG_ROW_LENGTH​ table option values:
ALTER​ ​ TABLE​ ​ tbl_name ​ ​ MAX_ROWS​ = ​ xxx ​ ​ AVG_ROW_LENGTH​ = ​ yyy ​ ;
If you do not know the current table option values, use ​ SHOW CREATE TABLE​ .
For the other errors, you must repair your tables. ​ myisamchk​ can usually detect and fix most
problems that occur.
The repair process involves up to four stages, described here. Before you begin, you should change
location to the database directory and check the permissions of the table files. On Unix, make sure
that they are readable by the user that ​ mysqld​ runs as (and to you, because you need to access the
files you are checking). If it turns out you need to modify files, they must also be writable by you.This section is for the cases where a table check fails (such as those described in ​ Section 7.6.2,
“How to Check MyISAM Tables for Errors”​ ), or you want to use the extended features that
myisamchk​ provides.
The ​ myisamchk​ options used for table maintenance with are described in ​ Section 4.6.3,
“​ myisamchk​ — MyISAM Table-Maintenance Utility”​ . ​ myisamchk​ also has variables that you can
set to control memory allocation that may improve performance. See ​ Section, “myisamchk
Memory Usage”​ .
If you are going to repair a table from the command line, you must first stop the ​ mysqld​ server. Note
that when you do ​ mysqladmin shutdown​ on a remote server, the ​ mysqld​ server is still available
for a while after ​ mysqladmin​ returns, until all statement-processing has stopped and all index
changes have been flushed to disk.
Stage 1: Checking your tables
Run ​ myisamchk *.MYI​ or ​ myisamchk -e *.MYI​ if you have more time. Use the ​ -s​ (silent) option to
suppress unnecessary information.
If the ​ mysqld​ server is stopped, you should use the ​ –update-state​ option to tell ​ myisamchk​ to
mark the table as “checked.”
You have to repair only those tables for which ​ myisamchk​ announces an error. For such tables,
proceed to Stage 2.
If you get unexpected errors when checking (such as ​ out of memory​ errors), or if ​ myisamchk
crashes, go to Stage 3.
Stage 2: Easy safe repair
First, try ​ myisamchk -r -q ​ tbl_name ​ (​ -r -q​ means “quick recovery mode”). This attempts to repair
the index file without touching the data file. If the data file contains everything that it should and thedelete links point at the correct locations within the data file, this should work, and the table is fixed.
Start repairing the next table. Otherwise, use the following procedure:
  1. Make a backup of the data file before continuing.
  2. Use ​ myisamchk -r ​ tbl_name ​ (​ -r​ means “recovery mode”). This removes incorrect
    rows and deleted rows from the data file and reconstructs the index file.
  3. If the preceding step fails, use ​ myisamchk –safe-recover ​ tbl_name ​ . Safe recovery
    mode uses an old recovery method that handles a few cases that regular recovery
    mode does not (but is slower).
    If you want a repair operation to go much faster, you should set the values of the
    sort_buffer_size​ and ​ key_buffer_size​ variables each to about 25% of your available memory
    when running ​ myisamchk​ .
    To get the mysql password of root
    get it from cd /root/serverguy/details/

Leave a Comment

Your email address will not be published. Required fields are marked *

Open chat