====== MYSQL Administrator ====== ===== Install Mysql Server ===== ==== Install with Yum ==== yum install mysql-server ==== Install from binary source ==== ==== Install from source ==== refer: * http://dev.mysql.com/doc/refman/5.6/en/installing-source-distribution.html * http://dev.mysql.com/doc/refman/5.7/en/installing-source-distribution.html Download source: Plsease select Generic Linux (Architecture Independent), Compressed TAR Archive package for download * newest source: https://dev.mysql.com/downloads/mysql/ * version 5.6: https://dev.mysql.com/downloads/mysql/5.6.html#downloads Below are steps to install MySQL Server **5.6.35**: - Step1: Add mysql user: useradd -r -U mysql -M -d /onec/mysql/data - Step2: Install depedency packages: yum install gcc-c++ yum install cmake yum install bison yum install ncurses-devel - Step3: Generate Makefile rm -f CMakeCache.txt make clean cmake . -DCMAKE_INSTALL_PREFIX=/onec/mysql -DMYSQL_DATADIR=/onec/mysql/data - Step4: Compile and Install make make install - Step5:chown to mysql: chown -R mysql.mysql /onec/mysql - Step6: copy my.cnf to /etc/my.cnf - Step7: Init database cd /onec/mysql scripts/mysql_install_db --datadir=/onec/mysql/data --user=mysql - Step8: Start Server: bin/mysqld_safe --user=mysql & or start with scripts: # Next command is optional cp support-files/mysql.server /etc/init.d/mysql.server /etc/init.d/mysql.server start - Step9: Login to Mysql server mysql -uroot - Step10: Reset password root mysqladmin password 'newpassword' ==== Installing MySQL on Microsoft Windows Using a noinstall Zip Archive ==== refer: http://dev.mysql.com/doc/refman/5.7/en/windows-install-archive.html Users who are installing from the noinstall package can use the instructions in this section to manually install MySQL. The process for installing MySQL from a Zip archive is as follows: - Step1: Extract the main archive to the desired install directory. For example: Extract to directory **d:\tools\mysql-5.7.13-winx64**(Optional: also extract the debug-test archive if you plan to execute the MySQL benchmark and test suite) - Step2: Create an option file: When the MySQL server starts on Windows, it **looks for option files in several locations**, such as the **Windows directory, C:\, and the MySQL installation directory**. MySQL looks for options in each location **first in the my.ini file, and then in the my.cnf file**. And in this step, we create the my.ini copy from my-default.ini with custom information: [mysqld] # set basedir to your installation path basedir=d:\\tools\\mysql-5.7.13-winx64 # set datadir to the location of your data directory datadir=d:\\tools\\mysql-5.7.13-winx64\data - Step3: Choose a MySQL server type - Step4: Initialize MySQL: To initialize the data directory, **invoke mysqld with the --initialize**(secure by default: including generation of a random initial root password) or **--initialize-insecure** in Installation Directory of MySQL server bin\mysqld --initialize bin\mysqld --initialize-insecure output: Init database mysql in data directory **d:\\tools\\mysql-5.7.13-winx64\data**. And with option **--initialize**, we check log **.err** in data directory to see **temporary password for root**:A temporary password is generated for root@localhost: 8mfak!jsyltB With old version: ./scripts/mysql_install_db --user=mysql ./bin/mysql_secure_installation -> set password for mysql - Step5: Start the MySQL server: bin\mysqld.exe --console - Step6: Install MySQL as service: d:\tools\mysql-5.7.13-winx64\bin\mysqld.exe --install - Step7: reset password root: ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; ===== MySQL Server Configuration ===== Check configuration file in linux: cat /etc/init.d/mysqld | grep cnf => output: # config: /etc/my.cnf Check configuration file in windows: * Go to services.msc to get property of service MySQLD * View the **Path To Execute**: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56 => configuration file is **C:\ProgramData\MySQL\MySQL Server 5.6\my.ini** ===== Basic commands ===== ==== Stop, start mysql server with mysqld ==== /etc/init.d/mysqld start /etc/init.d/mysqld stop mysqladmin -u username –p shutdown ==== Stop, start mysql server manual with mysqld_safe ==== start: /onec/mysql/bin/mysqld_safe --datadir="/onec/mysql/data" --pid-file="/onec/mysql/tmp/mysql.pid" ==== Drop, Create database with mysqladmin ==== mysqladmin -p drop database_name mysqladmin -p create database_name ==== Show MySQL version ==== * You only use MySQL client to connect MySQL Server to see the version: mysql -uroot -p Enter password: output: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.28 Source distribution * Or use SQL Query to see the version informations: SHOW VARIABLES LIKE "%version%"; output: +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | innodb_version | 1.1.8 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.28 | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------+ * Or use command STATUS: STATUS; output: mysql Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using EditLine wrapper Connection id: 2 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.28 Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /onec/mysql/tmp/mysql.sock Uptime: 28 min 53 sec Threads: 1 Questions: 14 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.008 ==== List of databases and tables with mysqlshow ==== === List of databases === mysqlshow -u root -p => output +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | test | +--------------------+ === change password of user === mysqladmin password 'newpassword' === get a list of the tables in that database === mysqlshow -u root -p mysql =>output: +---------------------------+ | Tables | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | ............................. ==== Show tables and procedure information ==== * show all tables in database: show tables; * show all procedures in database: SHOW PROCEDURE STATUS where Db='pp_ws_gunviet'; select Db,name,definer from mysql.proc where Db='pp_ws_gunviet'; * change definer of procedures update mysql.proc set definer='root@localhost' where Db='pp_ws_gunviet'; * Design of table DESCRIBE tablename; * show procedure SHOW CREATE PROCEDURE `sp_gold_update_transaction`; * change definer to **root@localhost** before import sql from sql dump file ==== Create database from script .sql ==== commands: root# mysqladmin -p create employee root# mysql -p employee < create_database.sql root# mysql -p employee < employee_data.sql ===== Managing User Privileges ===== ==== show all users ==== * show all users: select User,Host FROM mysql.user; * show privillege of users: show grants for onec@localhost; ==== Creating user accounts with GRANT and REVOKE ==== === Syntax === GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name | * | *.* | db_name.*} TO user_name [IDENTIFIED BY [PASSWORD] 'password'] [, user_name [IDENTIFIED BY 'password'] ...] [REQUIRE NONE | [{SSL| X509}] [CIPHER cipher [AND]] [ISSUER issuer [AND]] [SUBJECT subject]] [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # | MAX_UPDATES_PER_HOUR # | MAX_CONNECTIONS_PER_HOUR #]] The ON clause specifies the items we are granting privileges on. This can be a named table or a named database with all its tables (dbname.*). We can also specify *.*, which means all databases and all tables. If we specify *, the privileges are granted on the currently selected database. === Example === * Create user accounts and give users access to databases, tables, and functions: grant usage on * to luke@localhost identified by 'password'; * Grant for user all privileges: grant all privileges on dbname.* to 'anhvc'@'localhost' identified by 'xxxxx'; * Grant for user at any host: grant all privileges on *.* to 'root'@'%' identified by 'xxxxx'; ==== Change User's Password ==== set password for fred@localhost = password('newpassword'); ==== Delete user ==== We use command "DROP USER" => The statement removes privilege rows for the account from all grant tables. * Syntax DROP USER user; * Example DROP USER 'fred'@'localhost'; And delete all username with name and not host DROP USER 'fred'@'%'; ==== Show grants ==== show grants for fred@localhost; ==== Privilege Levels ==== The privileges we can grant using the GRANT statement can be divided into two basic categories: === user-level privileges === *CREATE: User can create tables. *CREATE TEMPORARY TABLES:User can create temporary tables. *DELETE:User can delete rows. *EXECUTE:User can execute procedures. *INDEX: User can create indexes. *INSERT:User can insert rows. *LOCK TABLES:User can lock tables. *SELECT:User can select rows. *SHOW DATABASES:User can execute a SHOW DATABASES command to retrieve the list of available databases. *UPDATE:User can update rows. *USAGE:User can log in, but cannot do anything else. === Administrator-level privileges === *ALL:User has all the privileges except WITH GRANT OPTION. *ALTER:User can alter tables. You may give this to some power users, but proceed with caution because it may be used to change the privilege tables. *DROP:User can drop tables. You may give this to trusted users. *FILE:User can load data from a file. Again, you may give this to trusted users. Beware of users trying to load arbitrary files, such as /etc/passwd or similar files! *PROCESS:User can show full process list—that is, see all the processes that MySQL is executing. *RELOAD:User can use the FLUSH statement. This has various purposes. We will look at FLUSH PRIVILEGES later in this chapter and will revisit FLUSH in Chapter 13. *REPLICATION CLIENT:User can check where the masters and slaves are. *REPLICATION SLAVE:Special privilege designed for the special replication user on the slave. See Chapter 16 for more details. *SHUTDOWN:User can run mysqladmin shutdown. For more information see Chapter 13. *SUPER: User can connect even if MySQL has its maximum number of connections and can execute the commands CHANGE MASTER, KILL (thread), mysqladmin debug, PURGE MASTER LOGS, and SET GLOBAL. *WITH GRANT OPTION:User can pass on any privileges he has. ===== System Admin Mysql Server ===== ==== Configuring Mysql Server ==== Some useful features are turned off by default, and it starts out insecure * Using InnoDB tables, so we need to perform a basic setup for these tables. * Turn on binary logging in all situations. This is highly useful for disaster recovery log-bin=filename * Turn on slow query logging. This tracks slow queries (as you might expect from the name) and will help you optimize your applications log-slow-queries=filename And you must create the file filename with owner is mysql: touch filename chown mysql.mysql filename * Configuration file /etc/my.cnf datadir=/var/lib/mysql log-error=/var/log/mysqld.log ==== Server Status and Variables ==== * To get information about the server and how it's running, we can look at the server status and the values of variables * The values of most of these variables can be set in your configuration file, from the command line when you start the server, or dynamically inside MySQL using the SET command. === Show status and variables === * Using query mysql> show status; mysql> show variables; * Using mysqladmin # mysqladmin -u root -p extended-status # mysqladmin -u root -p variables => output: ^ Variable_name ^ Value ^ | Aborted_clients | 0 | | Aborted_connects | 2 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 7366 | | Bytes_sent | 43671 | * query variable name show variables where variable_name='Port'; Some other particularly interesting values to look at are listed here: * threads_connected: This is the current number of connections to the server. * slow_queries: This is the number of queries this server has run that have taken more time than the value of the server variable long_query_time. These queries are logged in the Slow Query Log. We will return to slow queries in Chapter 19, "Optimizing Your Queries." * uptime: This is how long this server instance has been running in seconds. === Setting variables === The set statement is used to set the value of server variables—the same variables we could see via the show variables statement : set variable=value; For example, we might use: set sql_safe_updates=1; ==== Viewing Process Information And Killing ==== === View Process Information === You can see what processes are currently running on your server by running the following command inside MySQL: * Using query show processlist; * Using mysqladmin # mysqladmin -u root -p processlist => output: +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 73 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ === Killing Process or Thread === If we have a problematic thread (for example, a query that is taking forever, or a problem user), we can terminate the thread using query: mysqladmin kill process_id ==== Clearing caches ==== MySQL has a set of internal caches. These can be cleared using the flush and reset commands. * clear the query cache: flush query cache; => This will defragment the query cache, improving performance. * clear the query cache: reset query cache; => Rather than defragmenting the query cache, this will actually clear it altogether. ==== Understanding the log files ==== MySQL keeps various log files that may be useful to you. Most of these logs are not enabled by default, so if you want logging, you will have to switch it on.\\ These are the logs you can keep: * Error log: Tracks all the errors that have occurred. This one is logged by default and will appear in your data directory. The file is called hostname.err on Linux and mysql.err on Windows. You can set the location to something else with the option: [mysqld] ....... log-error=filename * Query log: Logs all the queries run on the system. You can turn on this log and specify the location with the option: [mysqld] ...... log=filename * Binary log: Logs all the queries that change data. This replaces the update log, which will still be around until MySQL version 5.0, but is deprecated. You can turn on this log and specify the location with the option: log-bin=filename * Slow query log: Logs all queries that took longer to execute than the value stored in the variable long_query_time. You can turn on this log and specify the location with the option: [mysqld] ...... log-slow-queries=filename The binary log can be viewed using: mysqlbinlog logfile ==== Query log of mysql server ==== * [windows] (You must go to **services.msc** and select service MySQL Server to see the **path of my.ini**) [mysqld] ....... log = "C:/genquery.log" And in MySQL 5.5 above, you turn on query log and change path of query log: general-log=1 general_log_file="C:/MySQL-ANHVC-PC.log" => You must self create file **C:/MySQL-ANHVC-PC.log** for writing * [linux] Show query log: - ON log in mysql query: SET GLOBAL general_log = 'ON'; - to OFF log, we set query: SET GLOBAL general_log = 'OFF'; - Show query log config in mysql server: show variables like '%general%'; output: +------------------+--------------------------------+ | Variable_name | Value | +------------------+--------------------------------+ | general_log | ON | | general_log_file | /onec/mysql/data/vdclinux6.log | +------------------+--------------------------------+ * Change default general log file: - Edit my.cnf [mysqld] ........ log=/var/log/mysqld.general.log - Create basic file log: touch /var/log/mysqld.general.log chown mysql.mysql /var/log/mysqld.general.log - Restart mysql: /etc/init.d/mysqld restart ==== Show slow log of mysql server ==== - Step1: Show slow log config in Mysql Server show variables like '%slow%'; output: +---------------------+-------------------------------------+ | Variable_name | Value | +---------------------+-------------------------------------+ | log_slow_queries | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /onec/mysql/data/vdclinux6-slow.log | +---------------------+-------------------------------------+ - Step2: Set config to enable slow log: SET GLOBAL slow_query_log = 'ON'; ===== MySQL Configuration ===== ==== MySQL default configuration ==== When we install **mysql-server** with yum in CentOS System, the default config .cnf will be installed to the directory **/usr/share/mysql/** ls /usr/share/mysql/my my-huge.cnf my-medium.cnf mysql_system_tables_data.sql my-innodb-heavy-4G.cnf my-small.cnf mysql_system_tables.sql my-large.cnf mysql_fix_privilege_tables.sql mysql_test_data_timezone.sql ==== Custom MySQL Configuration ==== Custom max connections: max_connections = 1024 max_user_connections = 1024 query_cache_size=8M join_buffer_size=1M thread_cache_size=16 table_open_cache=400 ===== Tunning Mysql Server ===== Before run script check, we need to run automatic all pages of websites for running all queries in mysql ==== MySQL table limit ==== https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.7/en/column-count-limit.html ==== Run script check ==== Download script from https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl and run: chmod +x mysqltuner.pl ./mysqltuner.pl Output: [OK] Currently running supported MySQL version 5.1.73 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +CSV +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 17M (Tables: 138) [--] Data in InnoDB tables: 58M (Tables: 402) [--] Data in MEMORY tables: 0B (Tables: 17) [!!] Total fragmented tables: 417 -------- Security Recommendations ------------------------------------------- [!!] User '@baby' has no password set. [!!] User '@localhost' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 40m 54s (11K q [4.487 qps], 257 conn, TX: 16M, RX: 3M) [--] Reads / Writes: 87% / 13% [--] Total buffers: 34.0M global + 2.7M per thread (151 max threads) [!!] Maximum possible memory usage: 449.2M (91% of installed RAM) [OK] Slow queries: 0% (0/11K) [OK] Highest usage of available connections: 2% (4/151) [!!] Key buffer size / total MyISAM indexes: 8.0M/29.3M [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts) [!!] Temporary tables created on disk: 27% (247 on disk / 898 total) [!!] Thread cache is disabled [!!] Table cache hit rate: 17% (64 open / 375 opened) [OK] Open file limit used: 0% (0/1K) [OK] Table locks acquired immediately: 100% (13K immediate / 13K locks) [!!] InnoDB buffer pool / data size: 8.0M/59.0M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Enable the slow query log to troubleshoot bad queries When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Set thread_cache_size to 4 as a starting value Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** key_buffer_size (> 29.3M) query_cache_size (>= 8M) tmp_table_size (> 16M) max_heap_table_size (> 16M) thread_cache_size (start at 4) table_open_cache (> 64) innodb_buffer_pool_size (>= 58M) Some information which you need to tuner: -------- Storage Engine Statistics ------------------------------------------- [!!] Total fragmented tables: 417 -------- Security Recommendations ------------------------------------------- [!!] User '@baby' has no password set. [!!] User '@localhost' has no password set. -------- Performance Metrics ------------------------------------------------- [!!] Maximum possible memory usage: 449.2M (91% of installed RAM) [!!] Key buffer size / total MyISAM indexes: 8.0M/29.3M [!!] Query cache is disabled [!!] Temporary tables created on disk: 27% (307 on disk / 1K total) [!!] Thread cache is disabled [!!] Table cache hit rate: 2% (64 open / 2K opened) [!!] InnoDB buffer pool / data size: 8.0M/59.0M ==== Tunning some basic parameters ==== General recommendations: - Run OPTIMIZE TABLE to defragment tables for better performance: * Check Information: [!!] Total fragmented tables: 417 * Tunning - Reduce your overall MySQL memory footprint for system stability - Set thread_cache_size to 4 as a starting value * Check Information: [!!] Thread cache is disabled * Tunning: Variables to adjust: - MySQL's maximum memory usage is dangerously high: * Check Information [!!] Maximum possible memory usage: 449.2M (91% of installed RAM) * Tunning - key_buffer_size (> 29.3M) * Check Information [!!] Key buffer size / total MyISAM indexes: 8.0M/29.3M * Tunning in /etc/my.cnf: [mysqld] ............. key_buffer_size=30M - query_cache_size (>= 8M) * Check Information [!!] Query cache is disabled * Tunning in /etc/my.cnf [mysqld] ............. query_cache_size=8M query_cache_limit=1M - query_cache_limit (> 1M, or use smaller result sets) * Check Information [!!] Query cache efficiency: 0.0% (0 cached / 18 selects) * - tmp_table_size (> 16M) * Check Information [!!] Temporary tables created on disk: 27% (323 on disk / 1K total) * Tunning with /etc/my.cnf tmp_table_size=32M - max_heap_table_size (> 16M) * Check Information max_heap_table_size=32M * Tunning - thread_cache_size (start at 4) * Check Information [!!] Thread cache is disabled * Tunning - table_open_cache (> 64) * Check Information [!!] Table cache hit rate: 3% (64 open / 1K opened) * Tunning in /etc/my.cnf [mysqld] ............. table_open_cache=100 - innodb_buffer_pool_size (>= 58M) * Check Information [!!] InnoDB buffer pool / data size: 8.0M/59.0M * Tunning === Thread_cache_size === refer: * http://www.epigroove.com/blog/optimize-mysql-the-thread-cache * http://www.dbasquare.com/kb/mysql-and-thread-cache-size/ MySQL uses a **separate thread for each client connection**. In environments where applications do not attach to a database instance persistently, but rather create and close a lot of connections every second, the process of spawning new threads at high rate may start consuming significant CPU resources. To alleviate this negative effect, MySQL implements **thread cache, which allows it to save threads from connections that are being closed and reuse them for new connections**. The parameter thread_cache_size defines how many unused threads can be kept alive at any time. === table_open_cache === refer: *https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/system-variables/optimizing-table_open_cache/ *http://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/ table_open_cache can be a useful variable to adjust to improve performance. Each concurrent session accessing the same table does so independently. This improves performance, although it comes at a cost of extra memory usage. table_open_cache indicates **the maximum number of tables the server keeps open**. Ideally, you'd like this cached so as **to re-open a table as infrequently as possible**. However, note that this is not a hard limit. When the server needs to open a table, it evicts the least recently used closed table from the cache, and adds the new table. If all tables are used, the server adds the new table and does not evict any table. As soon as a table is not used anymore, it will be evicted from the list even if no table needs to be open, until the number of open tables will be equal to table_open_cache. You can view the current setting in the my.cnf file, or by running: select @@table_open_cache; output: ^ @@table_open_cache ^ | 400 | To evaluate whether you could do **with a higher table_open_cache, look at the number of opened tables, in conjunction with the server uptime** (Opened_tables and Uptime status variables): show global status like 'opened_tables'; output: ^ Variable_name | Value ^ | Opened_tables | 354858 | If the number of opened tables is increasing rapidly, you should look at increasing the table_open_cache value. Try to find a value that sees a slow, or possibly even no, increase in the number of opened tables. The open table cache can be emptied with **FLUSH TABLES** or with the flush-tables or refresh mysqladmin commands. === tmp_table_size and max_heap_table_size === In some cases, MySQL creates **internal temporary tables while processing queries**. On the base of the dimensions of the resultset MySQL will use **the MEMORY engine AND/OR the MyISAM engine**. The difference is that **MEMORY engine will handle the table in memory**, while **MyISAM will write it on disk**. A table created using the MEMORY engine can be automatically converted by the MySQL server if it exceed the defined threshold The tables explicitly created with **CREATE TABLE ENGINE MEMORY** use ONLY **the max_heap_table_size** system variable to determines** how large the table is permitted to grow and there is no conversion to on-disk format**. Now let see what the manual say about how this two variables and their relation: * The **maximum size of internal in-memory temporary tables**. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) * If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. * **Increase the value of tmp_table_size (and max_heap_table_size if necessary)** if you **do many advanced GROUP BY queries and you have lots of memory**. This variable does not apply to user-created MEMORY tables. === Final my.cnf for tunning === key_buffer_size=30M query_cache_size=8M query_cache_limit=1M join_buffer_size=512K thread_cache_size=16 table_open_cache=400 And for connections to mysql server max_connections = 1024 max_user_connections = 1024 ===== Backing Up and Restoring Your Database ===== Bear in mind that although backups are vitally important, all backups involve restricting access to the user while backups are being made\\ One solution to this issue is **replication**. You can take down one slave and back it up while users continue blissfully about their business. There are four ways you can make a backup in MySQL: * Use the mysqldump script * Use the mysqlhotcopy script * Directly back up the data files yourself: This is really doing what mysqlhotcopy does, but manually. If you choose to use this option, you will need to either shut down the database or flush and lock all tables before copying to make sure that they are internally consistent. Both mysqldump and mysqlhotcopy will flush and lock for you, so they are easier, safer options. * Use the BACKUP TABLE and RESTORE TABLE commands to back up or restore a specified table or set of tables. ==== Use the mysqldump script and restore ==== * Backup(dump database): Create a dump file(a file containing the SQL statements necessary to re-create the database) mysqldump --opt -u username -p databasename > backup.sql example: mysqldump --opt -u root -p mysql > backup.sql * Backup(dump table): mysqldump --opt -u username -p databasename tablename > backup_table.sql * Backup Options: * Dump single transaction, routines(functions and procedures), triggers from the dumped databases: --single-transaction --routines --triggers * Backup(only table structure): --add-drop-database --no-data * Backup with fields in sql insert command --complete-insert * Backup with option skip comments and multiple sql insert command => For debugging changes in data rows of database --skip-comments --skip-extended-insert * Restore: We could reload or re-create the databasename database elsewhere by doing the following: * step1: Creating an appropriately named database on the target machine mysqladmin -u username -p create backup_mysql * step2: Loading this file using: mysql -f --default-character-set=utf8 --routines -u username -p backup_mysql < backup.sql ==== Use the mysqlhotcopy script ==== create a data file(This copies the data files for a particular database directly) ==== Examples for backup with dump ==== === Dump single database === * dump mysqldump -u'root' -p dbname > dbname.sql Or dump with single transaction, routines, triggers mysqldump -hlocalhost -uroot -p --add-drop-table --quote-names --opt --single-transaction --routines --triggers --add-locks --extended-insert --quick --compress dbname > dbname.sql * restore mysql -f --default-character-set=utf8 -u'root' -p dbname < dbname.sql === Dump structure of database === mysqldump --databases passport --routines --no-data -uroot -p mysqldump --all-databases --routines --no-data -uroot -p mysqldump --databases `cat alldb.txt` --routines --no-data -uroot -p > alldatabases.sql === Dump all databases === * dump all databases mysqldump -hlocalhost -u$USERNAME -p$PASSWORD -A --add-drop-table --quote-names --opt --routines --add-locks --extended-insert --quick --compress > dbname.sql * dump with list databases mysqldump -hlocalhost -u$USERNAME -p$PASSWORD --databases `cat alldb.txt` --add-drop-table --quote-names --opt --routines --add-locks --extended-insert --quick --compress > dbname.sql * dump with list databases for i in `cat alldb.txt `; do mysqldump -hlocalhost -uroot -pxxxx --add-drop-table --quote-names --opt --routines --add-locks --extended-insert --quick --compress $i> $i.sql; done === Import database === mysql -u$USERNAME -p$PASSWORD <<< "DROP DATABASE IF EXISTS \`$DB\`;" mysql -u$USERNAME -p$PASSWORD <<< "CREATE DATABASE \`$DB\`;" mysql -f --default-character-set=utf8 -u$USERNAME -p$PASSWORD $DB < dbname.sql load data infile '/tmp/Itemsale-VNG.csv' into table shopitemlist fields terminated by ','; ==== Backup and restore data files ==== (This method can cause error with Inno DB) Backup: - Step1: Stop mysqld /etc/init.d/mysqld stop - Step2: Copy data files to backup directory - Step3: Start mysql after backup finish: /etc/init.d/mysqld start Restore: - Step1: Install mysql-server have **the same version** with above server - Step2: Copy backup data files to mysql data - Step2: start mysqld which use above data files ==== Backup and Restore users in mysql ==== - step1: backup users in mysql mysql -uroot -pxxxx -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | while read uh; do mysql -uroot -pxxxx -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql - step2: restore: mysql -uroot -p < grants.sql ===== Modify Mysql database Design ===== ==== Modify column of table ==== * update define of column in table: ALTER TABLE `addmoney` MODIFY COLUMN `money` int(10) NOT NULL DEFAULT 0 AFTER `username`; * add new column **`chip` bigint(20) DEFAULT '0'** into table ALTER TABLE `addmoney` ADD COLUMN `chip` bigint(20) NOT NULL DEFAULT 0 AFTER `money`; ==== Change Table Name ==== ==== Compare changes in new database when compare with old database ==== - Step1: Dump old and new database with options below to remove comments and write all insert commands on multiple row: mysqldump --skip-comments --skip-extended-insert -uroot -p olddbname > olddbname.sql mysqldump --skip-comments --skip-extended-insert -uroot -p newdbname > newdbname.sql - Step2: Compare oldddname.sql and newdbname.sql to see changes ==== Using mysqldiff for comparing new database and old database ==== mysqldiff tools: * Github:https://github.com/onishi/mysqldiff * Download: git clone https://github.com/onishi/mysqldiff ===== Storage Engine ===== ==== MyISAM(non-transaction-safe tables) ==== * Data values are stored in machine format; this is fast, but machine/OS dependent * Can't handle tables larger than 4GB * data files: MyISAM table is stored on disk in three files: * .frm file stores the table definition * .MYD data file * .MYI index file * config in [my.cnf] datadir=/db/mysql ==== InnoDB(transaction-safe tables) ==== * Transaction Model and Locking:In InnoDB, all user activity occurs inside a transaction. * If the autocommit mode is enabled: each SQL statement forms a single transaction on its own. MySQL always starts a new connection with autocommit enabled. * If the autocommit mode is switched off with SET AUTOCOMMIT = 0, then we can consider that: * a user always has a transaction open. An SQL COMMIT or ROLLBACK statement ends the current transaction and a new one starts * A COMMIT means that the changes made in the current transaction are made permanent and become visible to other users. A ROLLBACK statement, on the other hand, cancels all modifications made by the current transaction. * Data files: InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions) => InnoDB tables can be of any size even on operating systems where file size is limited to 2GB. * Config innodb: innodb_data_home_dir = /ibdata innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend * create table: CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB; CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB; ==== Compare Transaction-safe tables (TSTs) And non-transaction-safe tables (NTSTs) ==== - Transaction-safe tables (TSTs) have several advantages over non-transaction-safe tables (NTSTs) * Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log. * You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled). Within a transaction with autocommit disabled, changes to non-transaction-safe tables still are committed immediately and cannot be rolled back. * You can execute ROLLBACK to ignore your changes (if autocommit is disabled). * If an update fails, all your changes will be restored. (With non-transaction-safe tables, all changes that have taken place are permanent.) * Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads - Examples from double-entry accounting systems often illustrate the concept of transactions. In double-entry accounting every debit requires the recording of an associated credit. If one writes a check for €100 to buy groceries, a transactional double-entry accounting system must record the following two entries to cover the single transaction: - Debit €100 to Groceries Expense Account - Credit €100 to Checking Account A transactional system would make both entries — or both entries would fail. By treating the recording of multiple entries as an atomic transactional unit of work the system maintains the integrity of the data recorded. In other words, nobody ends up with a situation in which a debit is recorded but no associated credit is recorded, or vice versa. ==== How to shrink/purge ibdata1 file in MySQL ==== That ibdata isn't shrinking is a particularly annoying feature of MySQL. The ibdata1 file can´t actually be shrunk unless you **delete all databases, remove the files and reload a dump**. - Step1: Do a mysqldump of all databases, procedures, triggers etc **except the mysql and performance_schema** databases - Step2: Drop all databases except the above 2 databases - Step3: Stop mysql - Step4: Delete ibdata1 and ib_log files - Step5: Start mysql - Step6: Restore from dump ===== Reset password of user in MySql server === - Step1: access database mysql with user root /sbin/mysql.server stop /usr/local/mysql/bin/mysqld_safe --skip-grant-tables & mysql --user=root mysql - Step2: Update password of any user: update user set Password=PASSWORD('9ioue87$#90') WHERE User='root'; ===== Replicate Config ===== ==== config master ==== * config in [/etc/mysql/my.cnf] log-bin = /var/log/mysql/mysql-bin.log binlog-do-db=exampledb server-id=1 (config log-bin:these logs are used by the slave to see what has changed on the master binlog-do-db=exampledb:replicate the database exampledb) * Create a user with replication privileges: GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY ''; * flush tables witch read lock: USE exampledb; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; ==== config slave ==== * transfer database from master to slave: use mysqldump * config replicate /etc/mysql/my.cnf: server-id=2 master-host=192.168.0.100 master-user=slave_user master-password=secret master-connect-retry=60 replicate-do-db=exampledb master-port = 3306 * connect to master SLAVE STOP; CHANGE MASTER TO MASTER_HOST='192.168.0.100',MASTER_PORT=3306, MASTER_USER='slave_user', MASTER_PASSWORD='', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183; ===== Upgrade MySQL Server ====== refer: https://dev.mysql.com/doc/refman/5.6/en/upgrading.html ===== Start MySQL Server with multiple Instances on the same host ==== ==== Start the Instance ==== - Step1: Create file /etc/mylocal.cnf from /etc/my.cnf and edit it with content below: [mysqld] datadir=/data/mysql socket=/var/lib/mysqllocal/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqldlocal.log pid-file=/var/run/mysqld/mysqldlocal.pid - Step2: Copy database from old mysql server to /data/mysql: cp -r /var/lib/mysql/* /data/mysql/ chown -R mysql.mysql /data/mysql - Step3: Start other instance with command below: /usr/bin/mysqld_safe --defaults-file=/etc/mylocal.cnf --port=3307 & ==== Stop the Instance ==== kill `cat /var/run/mysqld/mysqldlocal.pid` ==== Connect to new MySql server ==== Add both option --port and --protocol(or --host) mysql -uroot -p --port=3307 --protocol=tcp ===== Character set and Collation ===== A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set. For example, character set latin1 (``ISO-8859-1 West European'') has the following collations: Collation Meaning latin1_bin Binary according to latin1 encoding latin1_danish_ci Danish/Norwegian latin1_german1_ci German DIN-1 latin1_german2_ci German DIN-2 latin1_swedish_ci Swedish/Finnish latin1_general_ci Multilingual ==== View charater set information in sql server ==== * Show character set: mysql> show character set; output: ^ Charset ^ Description ^ Default collation ^ Maxlen ^ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | * Config in /usr/share/mysql/charsets/Index.xml: East Asian GBK Simplified Chinese cp936 primary compiled binary compiled Unicode UTF-8 Unicode utf-8 primary compiled binary compiled * Show collation: mysql> show collation; output: ^ Collation ^ Charset ^ Id ^ Default ^ Compiled ^ Sortlen ^ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | | Yes | 1 | => not install gbk_chinese_ci * SHOW VARIABLES LIKE 'character_set_database': ^ Variable_name ^ Value ^ | character_set_database | latin1 | ==== Install character set ==== Refer: http://ific.uv.es/informatica/manuales/MySQL-4.1.1/manual_Charset.html#Charset-defaults Install character set gbk: ./configure mysql with option: --with-extra-charsets=gbk ===== Some issues ===== - issues 1: * Run command: # mysqlshow => mysqlshow: Access denied for user 'root'@'localhost' (using password: NO) * Resolve: According to the error message, root@localhost has a password, but you aren't supplying it. Add -p to your commands to be prompted for the password. normal user: mysqlshow -u root -p root user: mysqlshow -p - issue 2: * Run command: telnet 192.168.191.128 3306 => Host '192.168.191.1' is not allowed to connect to this MySQL server * Resolve: grant usage on * to anhvc@192.168.191.128 identified by 'password'; grant all privileges on *.* to 'ztgame'@'10.30.31.%' identified by 'ztgame'; - issue3: the table CHARBASE is full when the size of data on disk harddriver is 4G * Get satus: show table status; show table status like 'CHARBASE'; - issue4: **PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away**: Edit my.ini [mysqld] max_allowed_packet = 16M => for import sql file. And [mysqldump] max_allowed_packet = 16M => for dump sql file