mysql dba certification

Tags: mysql heartbeat1.Heartbeat IntroductionHeartbeat is a Linux open source, widely used high-availability cluster system, since 1999 to now, released a number of versions, is currently open source Linux-ha project The most successful example, in the industry has been widely used.1.1 Heartbeat EffectHeartbeat can transfer resources (such as IP and program services) from one failed computer to another on a functioning machine, which is generally refe

-create-info –-extended-insert –-default-character-set=latin1 dbname>alltable.sqlParameter description:--quick: Used to dump large tables, forcing mysqldump to retrieve data from the server one row at a time instead of retrieving all rows and outputting the cache to memory.--no-create-info: The Create TABLE statement is not created.--extended-insert: Using a multiline insert syntax that includes several values lists, so that the file is smaller, and the IO also small import data is very fast.--d

= ' Linzhongniao1 ' # # # # # # # # # # @1=3 # # @2= ' Linzhongniao2 ' # # # UPDATE ' Linzhongniao '. ' Student ' # # # WHERE # # # @1=5 # # # @2= ' Linzhongniao1 ' # # # # # # # # @1=5 # # @2= ' Linzhongniao2 ' # # # UPDATE ' Linzhongniao '. ' Stude NT ' # # # WHERE # # # @1=6 # # # # @2= ' Linzhongniao1 ' # # # # # # # # @1=6 # # @2= ' Linzhongniao2 ' # # # UPDATE ' Linzhongniao '. Tudent ' # # # WHERE # # # @1=8 # # # # # # # @2= ' Linzhongniao1 ' # # # # # @1=8 # # @2= ' Linzhongniao2 ' # #

Tags: MySQL database database administrator database development Beijing MySQLMySQL database business in some data similar to data dictionary, such as configuration file configuration, common business configuration or table with little change in data volume, these tables are often not particularly large, but most of the business scenario will be used, then this table is suitable for mycat global table, do not need to slice the data , just keep a copy

, and execute the statement by displaying the specified table partition . table partition types are:1.rangeTable partition: Range table partition, according to a certain range of values to determine the data contained in each partition ; 2.listtable partition: Table partition, according to a certain value to determine the data contained in each partition ; 3.Hash Table partition: Hashtable partition, according to a custom function return value to determine the data contained in each partition

affected (0.00 sec)[emailprotected] 04:5907->flush privileges;Query OK, 0 rows affected (0.00 sec)Under normal circumstances authorized SELECT,INSERT,UPDATE,DELETE4 permissions, and some open source software, such as Discuzbbs, but also need to create,drop and other more dangerous permissions.2. After the database table is generated, the Create and drop authorizations are retracted[emailprotected] 04:5925->help revokeREVOKE ALL PRIVILEGES, GRANT OPTIONFROM user [, user] ...REVOKE INSERT ON *.*

. Check the statement of the built-in table (you can see the index and the information about creating the table)show create table student\G[emailprotected] 12:2308->show create table student\G*************************** 1. row *************************** Table: studentCreate Table: CREATE TABLE `student` ( `id` int(4) NOT NULL, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT ‘0‘, `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

, delimited)Slave End:--REPLICATION-DO-DB setting the database to be replicated (multiple databases, delimited)--REPLICATION-IGNORE-DB setting ignores replicated databases (multiple databases, delimited)--replication-do-table setting the table to be copied (multiple tables, delimited)--replication-ignore-table setting ignores replicated tables (multiple tables, delimited)--replication-wild-do-table is the same as the replication-do-table function, but it can be added with wildcard characters.--r

Dual master configuration 3.Mysql dual master+ Concou +keepalived Configure high-availability database client as a DBA, be sure not to use a variety of GUI tools, MySQL with command line client is your best weapon optional 1.mysql partition configuration, Because there are too many restrictions, the actual ta

: *-S, is to indicate the way in which the C, T, L, R is sorted according to the number of records, time, query time, the number of records returned, AC, at, AL, AR, indicating the corresponding flashback; *-T, is the meaning of top N, that is, to return the number of previous data; *-G, you can write a regular matching mode, the case is not sensitive; Like what /path/mysqldumpslow-s r-t 10/database/mysql/slow-log Gets the 10 queries that return the

Tags: middle slave admin and dba _id default tab REPLICATThere are 3 different formats for binary logs (Binlog) in MySQL 5.5: Mixed,statement,row, the default format is Statement. Summarize the pros and cons of these three format logs.MySQL Replication replication can be based on a single statement (Statement level), or based on a record (Row level), you can set the replication levels in

for DRBD combat (18 knots) 14-14th-old boy MySQL high availability integrated implementation combat (22 knots) 15-15th-old boy MySQL database optimization thought and optimization combat (9 knots) 16-16th-old boy MySQL business change process and security management ideas (7) Old Urchin MySQL How to obtain: Cli

time. With logical backups, the longer the recovery time, the greater the impact on the business. So, we try to use a physical backup, such as Xtrabckup.Backup storage is divided into: Local storage Remote Storage If our backup database is stored on a local server, there may be a backup file that is not available and can result in loss of data.Therefore, we have to prioritize remote backup, you can back up the MySQL data files to a

: To disconnect a MySQL connection, you can use the Mysqliclose () function to disconnect from the MySQL server, which has only one parameter for the Mysqliconnect () function to create a MySQL connection identifier that is returned after a successful connection. Grammar:BOOL Mysqli_close (mysqli $link) This function closes the non-persistent connec

This is my study note for my old boy teacher's MySQL DBA Advanced OPS course, the old boy teacher spoke very well and thanked the old boy teacher very much. Just contact operations have a lot of knowledge, if I publish the article has incorrect place, please operation and maintenance industry elite, teacher timely guidance, hehe ~ I will also update their own learning Ysql

| | NULL| || age | tinyint(2) | NO | | 0 | || dept| varchar(16) | YES | | NULL| || sex | char(4) | YES | | NULL| |+---------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)9.13 Changing table names9.13.1 Rename Method1. Command syntax: Rename table name to new table name;Columns such as: Table student name changed to usermysql> show tables;+---------------+| Tables_in_zbf |+---------------+| student |+---------------+1 row in set (0.00 sec)

L006-oldboy-mysql-dba-lesson06 data cleanup status, marked first Update table state=1 , and then delete. MyISAM no foreign keys, hardware, concurrency, lock tab

‘;Do not add conditions to be very careful, professional practice, be sure to ask the development of confirmation, if you send the development of the statement to be marked in parentheses, to prevent the DBA misunderstanding.B. All data has been changed[emailprotected] 03:0109->select * from test;+----+--------+| id | name |+----+--------+| 1 | tiejun || 2 | tiejun || 3 | tiejun || 4 | tiejun || 5 | tiejun |+----+--------+C. Restoring with back

| woshishei || 5 | nimei |G. Test completed, exit the database, and then back up the above data, save the backup. Check the contents of the backed up SQL data after backup and filter out useless information.[[emailprotected] ~]# mysqldump -usystem -pzbf666 -B wwn >/opt/zbf_bak.sql[[emailprotected] ~]# grep -E -v "#|\/|^$|--" /opt/zbf_bak.sqlH. A wrong backup case, if you do not check the backup data may cause the data is not desired[[emailprotected] ~]# mysqldump -usystem -pzbf666 -A -B wwn >/