MySQL decentralized database
2. From the previous one, it is recommended to use the replication mechanism of Mysql to establish a master slave for replication. At the beginning, I misunderstood and used amoeba's virtual dB (load balancing pool) as the writepool. As a result, the data that should have been inserted into the same table was split and written into different physical databases. This is naturally inconsistent with the semantics of the
3. Amoeba has realized the vertical and horizontal segmentation of data. In terms of horizontal segmentation, the granularity is row. Using SQL JEP statements can design complex segmentation rules, I think it is more powerful. The granularity of vertical segmentation is table. Requests for different tables can be sent to different nodes for execution, but columns cannot be used as the granularity of fragmentation. According to the author, amoeba does not do SQL parsing and rewriting. Under the current mechanism, it seems that it is difficult to realize the distribution of different columns in the same table on different nodes. However, for developers, a well-designed table structure should be able to achieve simple load balancing based on relational attributes.
1. Open the computer and find MySQL in the start interface of the desktop, as shown in the figure
if it is MyISAM, there will be three kinds of files. Frm. MyD. MYI in the data directory. Then move the data directly and configure the permissions to restore it
if it's InnoDB, it's quite annoying. In the data directory, there is only. Frm (this is only a data format). After this is moved, the original data cannot be restored
the real data is in iddata1 under the data directory. Therefore, if there is this file, then there is still hope to restore it
however, I tried all the articles on the Internet and still said "there are no tables in the database"
in the end, I found the answer. That's my.ini's configuration file. There is an item:
InnoDB_ data_ home_ dir=" C:\ Program Files\ MySQL\ MySQL Server 5.1\ data"
just set the above address to your correct one
remember to restart MySQL (enter net stop MySQL in CMD, and then net start MySQL) to restore
it took me a day to find the problem, hoping to help my friends who are also in trouble
to sum up, it is better to use MyISAM by default for MySQL, which is easy to transfer. Database and database are separated and will not be mixed together. So you can set the default in my.ini:
default storage engine = MyISAM
generally, you need to input the IP address, port number, user name and password of the target server
for example: IP address: 192.168.11.101
port: 3306
User Name: root
password: root
user name and password are generally entered into root. If not, go to the database on the target server to manage the users
MySQL generally uses native for MySQL. Click the connect button in the upper left corner. Just input the above information. Remember to Ping the network first.
then open the background management interface of the website through the browser, and log in with the user name and password given by Xinwang. There is a MySQL database management entry at the bottom
after entering the background of the new network, you need to input the password again and verify it again. You can input it just as you started. My MySQL database has no direct import function, so all the code just edited in Dreamweaver to the SQL execution window and click execute! The next step is to modify the link code in the website file, and change the corresponding server address, user name and password into the corresponding one
The default storage location of database files is C: / / program files / MySQL / MySQL Server 5.0 / data
the configuration file of the database is in C: / / program files / MySQL / MySQL Server 5.0 / my.ini
the reason why you can't find the database folder and the folder you created in the data folder is that the test database is empty and is used for testing. You need to go to the MySQL database to find your own folder
the default location of MySQL database files is C: / / program files / MySQL / MySQL Server 5.0 / MySQL
MySQL is an open source relational database management system (RDBMS). MySQL database system uses the most commonly used database management language -- structured query language (SQL) for database management
here are several commands used to find different databases:
1. Select the database you created
MySQL & gt; USE MYSQLDATA; ( Press enter and database changed will show that the operation is successful!)
2: use show statement to find out what database currently exists on the server
MySQL & gt; SHOW DATABASES;
3. View the existing tables in the current database
MySQL & gt; SHOW TABLES;
4. The structure of display table
mysql> DESCRIBE MYTABLE;
MySQL database server has three databases: information_ Schema database, MySQL database, test database
1、nformation_ Schema database: this database holds the information of all databases of MySQL server. For example, the name of the database, the table of the database, the access rights, the data type of the database table, the information of the database index, and so on. Every bit of information about this database is stored in this database
nformation_ Schema database comes with MySQL, which provides a way to access database metadata. What is metadata? Metadata is data about data, such as database name or table name, data type of column, or access right, etc. Sometimes other terms used to express this information include "data dictionary" and "system catalog."
in mysql, the_ Schema is regarded as a database, or rather an information database. It holds information about all other databases maintained by the MySQL server
such as database name, database table, data type and access permission of table column, etc. In information_ In schema, there are several read-only tables. They are actually views, not basic tables, so you won't be able to see any files associated with them
2. MySQL database: this database contains all the information tables in the MySQL database
this is the core database of MySQL, which is similar to the master table in SQL server. It is mainly responsible for storing the control and management information that MySQL needs to use, such as database users, permission settings, keywords, etc. You can't delete it. If you don't know much about mysql, don't easily modify the table information in the database
3. Test database: an empty database for testing
this is a test database created at the time of installation. Like its name, it is a completely empty database without any tables and can be deleted
backup: mysqlmp - H 127.0.0.1 - U root - P test1 & gt; sql_ Bak. SQL
restore: MySQL - H 127.0.0.2 - U root - P - P 3306 test1 & lt; sql_ bak.sql
After the operation is logic, everything is demand and realization. I hope this article can help you understand the isolation level from the perspective of requirements, status quo and solutions
generation of isolation level
under the condition of string execution, the order of data modification is fixed and predictable, but in the case of concurrent execution, the data modification is unpredictable and not fixed. In order to achieve a fixed and predictable result in the case of concurrent execution, the isolation level is generated
so the isolation level is used to balance database concurrent access and data consistency
four isolation levels of transaction
read uncommitted read, uncommitted data can be read. Read committed has committed a read. For lock read (select with update or for share), update and delete statements, InnoDB only locks index records, but does not lock the gap between them, so it allows new records to be freely inserted next to the locked records. Gap locking is only used for foreign key constraint checking and plicate key checking. Repeatable read can be read repeatedly. Consistent read in a transaction reads the snapshot created by the first read of the transaction. Serializable serialization
after understanding the requirements of four isolation levels, on the basis of using lock to control isolation level, we need to understand the locked object (data itself & amp; data itself); Gap) and understanding the full set composition of the entire data range
the complete set of data range is composed of
sql statements, which judge the data range that does not need to be scanned according to the conditions (without locking)
the range of data scanned by SQL statement according to conditions that may need to be locked
taking a single data range as an example, the data range complete set includes: (the data range is not necessarily a continuous value, it may also be composed of interval values)
1. The data has filled the entire data range: (the data range is completely filled, there is no data gap)
< UL >shaping, the data range 1-5 with unique constraints on the value,
existing data 1 2. 3, 4, 5. At this time, the data range has been completely filled
shaping, data ranges 1 and 5 with unique constraints on values,
existing data 1 and 5, at this time, the data range has been completely filled
2. Data fills part of the data range: (the data range that is not completely filled is the data gap)
the data range of shaping is 1-5,
there are data 1, 2, 3, 4, 5, but because there is no unique constraint,
so the data range can be repeatedly filled by data 1-5
shaping, the data range with the unique constraint condition is 1-5,
has data 2, 5, at this time, the data range is not completely filled, and 1, 3, 4 can also be filled
3. There is no data in the data range (there is a gap)
as follows:
the data range of shaping is 1-5, and there is no data in the data range at present
after understanding the composition of the complete data set, let's look at the problems caused by transaction concurrency
problems caused by uncontrolled concurrency
if concurrent transactions are not controlled, they will bring some problems, mainly including the following situations
1. Due to the change of existing data in the scope:
Update loss: when multiple transactions select the same row, and then update the row based on the initially selected value,
because each transaction does not know the existence of other transactions, the final update will cover the updates made by other transactions
dirty read: a transaction is modifying a record, which is in inconsistent state before the transaction is completed and committed
at this time, another transaction also reads the same record. If it is not controlled,
the second transaction reads these "dirty" data and further processes them accordingly, the submitted data dependency will be generated
This phenomenon is called "dirty reading"2. The amount of data in the range has changed, resulting in:
can't be read repeatedly: a transaction reads the previously read data again at a certain time after reading some data,
finds that the read data has changed, or some records have been deleted
This phenomenon is called "non repeatable reading"unreal reading: a transaction rereads the previously retrieved data according to the same query conditions, but finds that other transactions insert new data that meets its query conditions. This phenomenon is called "unreal reading"
it can be simply considered that the amount of data that meets the conditions has changed
because uncontrolled concurrency will bring a series of problems, which will lead to the results that we need. So we need to control concurrency to achieve the desired result (isolation level)
implementation of MySQL isolation level
InnoDB supports these isolation levels through locking policy
row locks include:
record locks
index record locks. Index record locks always lock index records, even if no index is defined in the table.
in this case, InnoDB creates a hidden clustered index and uses the index to lock records
gap locks
gap locks are locks on gaps between index records, or locks before the first record or after the last record
gap locking is part of the trade-off between performance and concurrency
for data range without gap, gap lock is not needed because there is no gap
next key locks
the combination of the record lock on the index record and the gap lock before the index record
assume that the index contains 10, 11, 13 and 20
the possible next key locks include the following intervals, in which the parentheses indicate that the interval endpoint is not included, and the square brackets indicate that the interval endpoint is included:
slide left and right to view
& quot; Supremum & quot; The value of the pseudo record is higher than any actual value in the index
Thesupremum is not a real index record, so in fact, the next key only locks the gap after the maximum index value
based on this, when all data ranges have been filled in the acquired data range, then there is no gap and gap lock is not needed
if there is a gap in the data range, it is necessary to confirm whether to lock the gap according to the isolation level
the default repeatable read isolation level. In order to ensure repeatable reading, in addition to locking the data itself, it is also necessary to lock the data gap
read committed committed, the record lock of mismatched row is released after MySQL evaluates where condition
for the update statement, InnoDB executes & quot; semi-consistent" Read, so that it will return the latest submitted version to MySQL,
so that MySQL can determine whether the line matches the where condition of update
summary & amp; Extension:
there is a unique constraint on the unique index, so if the changed data violates the principle of unique constraint, it will fail
when the where condition uses the secondary index to filter data, it will lock the entries hit by the secondary index and the corresponding clustered index; Therefore, when other transaction changes hit the locked cluster index, they will wait for the lock
The increase of row lock is row by row, so it may lead to deadlock in concurrencyfor example,
when session a locks a qualified cluster index, Session B may have held the record locks of the cluster index, while Session B is waiting for the record locks of a cluster index held by session a
session a and Session B are clustered indexes located by two unrelated secondary indexes
session a through index IDA, Session B through index IDB
when there is no gap in the data obtained by where condition, there will be no gap lock no matter the isolation level is RC or RR
for example, the data range that has been fully filled is obtained through the unique index, and the gap lock is not needed at this time
the purpose of gap lock is to prevent data from being inserted into the gap, so the existence of data in the gap caused by insert or update changes will be blocked
InRC isolation level mode, query and index scanning will disable gap locking. At this time, gap locking is only used for foreign key constraint checking and plicate key checking (mainly uniqueness checking)
InRR mode, in order to prevent unreal reading, gap locks will be added
in a transaction, the lock is added at the beginning of SQL and released at the end of the transaction
as far as lock type is concerned, there should be optimization lock and lock upgrade. For example, if RR mode does not use index query, can it be directly upgraded to table lock
as far as the lock application scenario is concerned, in the playback scenario, if it is determined that the transaction can be concurrent, it can consider not locking to speed up the playback
lock is only a granularity of concurrency control, which is only a small part:
whether concurrency control is needed in different scenarios (known changes of non intersection and orderly data, concurrent playback of multiple transactions with the same front-end transaction in MTS of MySQL)
< P > granularity of concurrency control (lock is a logical granularity, There may also be physical layer and other logic granularity or mode)optimization under the same granularity, (lock itself has optimization, such as IX, is type optimization lock)
security of granular loading & amp; Performance (such as acquiring page lock before acquiring row lock, and releasing page lock after acquiring row lock, no matter whether the acquisition is successful or not) and so on
If you want to use any library, write the library name
< blockquote >use
< / blockquote > and you can switch to it