11 advanced MySQL questions
Explain MySQL architecture
. - The front layer
takes care of network connections and security authentications, the
middle layer does the SQL query parsing, and then the query is handled
off to the storage engine. A storage engine could be either a default
one supplied with MySQL (MyISAM) or a commercial one supplied by a
third-party vendor (ScaleDB, InnoDB, etc.)
Explain MySQL locks
. - Table-level locks allow the
user to lock the entire table, page-level locks allow locking of
certain portions of the tables (those portions are referred to as
tables), row-level locks are the most granular and allow locking of
specific rows.
Explain multi-version concurrency control in MySQL
.
- Each row has two additional columns associated with it - creation
time and deletion time, but instead of storing timestamps, MySQL stores
version numbers.
What are MySQL transactions?
- A set of instructions/queries that should be executed or rolled back as a single atomic unit.
What’s ACID?
- Automicity - transactions are
atomic and should be treated as one in case of rollback. Consistency -
the database should be in consistent state between multiple states in
transaction. Isolation - no other queries can access the data modified
by a running transaction. Durability - system crashes should not lose
the data.
Which storage engines support transactions in MySQL?
- Berkeley DB and InnoDB.
How do you convert to a different table type?
- ALTER TABLE customers TYPE = InnoDB
How do you index just the first four bytes of the column?
- ALTER TABLE customers ADD INDEX (business_name(4))
What’s the difference between PRIMARY KEY and UNIQUE in MyISAM?
- PRIMARY KEY cannot be null, so essentially PRIMARY KEY is equivalent to UNIQUE NOT NULL.
How do you prevent MySQL from caching a query?
- SELECT SQL_NO_CACHE …
What’s the difference between query_cache_type 1 and 2?
- The second one is on-demand and can b
Ïà¹ØÎĵµ£º
1 °²×°MySQL5
ÓÃÏÂÁÐÃüÁî°²×°MySQL£º
yum install mysql mysql-server
È»ºóÎÒÃÇΪMySQL´´½¨ÏµÍ³Æô¶¯Á´½Ó£¨ÕâÑùµÄ»°£¬MySQL¾Í»áËæ×ÅϵͳÆô¶¯¶øÆô¶¯£©£¬²¢Æô¶¯MySQL·þÎñÆ÷£º
chkconfig –levels 235 mysqld on
/etc/init.d/mysqld start
ÔËÐÐ
mysqladmin -u root password yourrootsqlpassword
À´ÎªrootÓû§ÉèÖà ......
ΪʲôѡÔñÔ´Âë±àÒë°²×°mysql£ºÎÒÔÚʹÓÃrpm»òÕßÊÇyum°²×°Ê±myql£¬ÀÏÊÇÌáʾÎÒÕÒ²»µ½*.sockÎļþ£¬ÕÒÁ˵ã
×ÊÁÏ˵ÊǸðüÊǷǹٷ½°æ±¾£¨Ã÷Ã÷Êǹٷ½ÏÂÔØ µÄ£©£¬ÆäÖÐÔÒòµ½ÏÖÔÚ»¹Ã»ÓиãÇå³þ¡£Ï£ÍûÖªÇéÈËÊ¿¸æÖª¡££¨¿ªÊ¼±àÒëµÄʱºò£¬ÏµÍ³»·¾³Ò»¶¨ÒªÅäÖúã¬GCC£¬GC++...µÈµÈµÄ±àÒëÆ÷Ò»¶¨Òª×öºÃ,±¾È˵ÄcentosÓÉÓÚ¿ªÊ¼
ϵͳ»·¾ ......
Á¬½Ó´úÂë1Ö±½ÓÊäÈë
<%
Dim my_conn, sql,rs
Set my_conn = createobject("ADODB.Connection")
my_conn.open = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_ '·þÎñÆ÷Ãû
& "DATABASE=mybase;"_ 'Êý¾Ý¿âÃû
& "UID=root;PWD=111; OPTION=35;" 'Óû§ÃûºÍÃÜÂë
Set rs = Server.Crea ......
ÐÞ¸Älinux·þÎñÆ÷µÄhttpÅäÖÃÖ®ºó£¬±ØÐëÖØÆôApache·þÎñ£¬ÃüÁîΪ£º /etc/rc.d/init.d/httpd restart
chown -R mysql:mysql Ŀ¼Ãû ¸Ä±äÎļþÊôÐÔ
mysqladmin -u root -p password 'new password' ÉèÖÃÃÜÂë
apt-get remove ɾ³ýÈí¼þ
apt-get clean ÇåÀí
/etc/rc.d/init.d/mysqld restart
php ¾ÍÅäÖÃÁËsession Ö÷ ......
±³¾°£ºÄ³¸öϵͳµÄmysqlÊý¾Ý¿âdnname²ÉÓÃĬÈϵÄlatin1×Ö·û¼¯£¬ÏµÍ³Éý¼¶ÐèÒª½«ËùÓÐÊý¾Ýת»»³Éutf-8¸ñʽ£¬Ä¿±êÊý¾Ý¿âΪnewdbname(½¨¿âʱʹÓÃutf8)
·½·¨Ò»£º
²½ÖèÒ» ÃüÁîÐÐÖ´ÐУºmysqldump --opt -hlocalhost -uroot -p*** --default-character-set=lantin1 dbname > /usr/local/dbname.sql
²½Öè¶þ ½« dbname.s ......