Three implementation methods of Mysql copy table and grant analysis

How to quickly copy a table

First, create a table db1.t and insert 1000 rows of data. At the same time, create a table db2.t with the same structure.

Suppose that we need to export the data rows with a>900 in db1.t and insert them into db2.t

mysqldump method

Several key parameter notes:

• –single-transaction is used to export data without locking the table db1.t.
• Method to START TRANSACTION WITH CONSISTENT SNAPSHOT;
• –no-create-info means that there is no need to export the table structure;
• –result-file specifies the path of the output file, where client means that the generated file is on the client machine.
  

Export csv file

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

This statement will save the result on the server. If the client where you execute the command and the MySQL server are not on the same machine, the t.csv file will not be generated in the temporary directory of the client machine.

This command will not overwrite the file for you, so you need to make sure that the file /server_tmp/t.csv does not exist, otherwise an error will be reported when the statement is executed because a file with the same name exists.

After getting the .csv export file, you can use the following load data command to import the data into the target table db2.t.

load data infile '/server_tmp/t.csv' into table db2.t;

Open the file /server_tmp/t.csv, use the tab character (\t) as the delimiter between fields and the newline character (\n) as the delimiter between records, and read the data;

Start a transaction.

Determine whether the number of fields in each row is the same as that in table db2.t:

• If they are different, an error is reported directly and the transaction is rolled back;
• If they are the same, a row is constructed, the InnoDB engine interface is called, and the data is written into the table.

Repeat step 3 until the entire /server_tmp/t.csv file is read in and commit the transaction.

Physical copy method

The mysqldump method and the method of exporting CSV files are both logical data import methods, that is, reading data from table db1.t, generating text, and then writing it to the target table db2.t. Is there a way to physically import data? For example, is it feasible to directly copy the .frm file and .ibd file of the db1.t table to the db2 directory? The answer is no.

Because, in addition to containing these two physical files, an InnoDB table also needs to be registered in the data dictionary. If you copy these two files directly, the system will not recognize and accept them because there is no db2.t table in the data dictionary.

MySQL 5.6 introduced the transportable tablespace method, which can achieve the function of physically copying tables by exporting and importing tablespaces.

Assume that the current goal is to copy a table r that is identical to table t in the database of db1. The specific execution steps are as follows:

• Execute create table r like t to create an empty table with the same table structure.
• Execute alter table r discard tablespace, then the r.ibd file will be deleted
• Execute flush table t for export and a t.cfg file will be generated.
• Execute the following two commands in the db1 directory: cp t.cfg r.cfg; cp t.ibd r.ibd;
• Execute unlock tables, and the t.cfg file will be deleted;
• Execute alter table r import tablespace and use the r.ibd file as the new tablespace for table r. Since the data content of this file is the same as that of t.ibd, table r will have the same data as table t.
  

Advantages and disadvantages of these three methods

The physical copy method is the fastest, especially for copying large tables. However, it must be a full copy, not a partial copy. Data needs to be copied to the server. It cannot be used when the user cannot log in to the database host. In addition, both the source table and the target table must use the InnoDB engine.

When using mysqldump to generate a file containing INSERT statements, you can add filtering conditions to the where parameter to export only part of the data. One of the shortcomings of this method is that it cannot use more complex where conditions such as join.

The select ... into outfile method is the most flexible and supports all SQL writing methods. However, one of the disadvantages of this method is that only one table's data can be exported at a time, and the table structure also needs to be backed up separately with another statement.

The latter two are logical backup methods and can be used across engines.

mysql global permissions

SELECT * FROM MYSQL.USER WHERE USER='UA'\G Display all permissions

The scope is the entire MySQL, and the information is stored in the user table of MySQL

Grant user ua the highest authority:

grant all privileges on *.* to 'ua'@'%' with grant option;

This grant command does two things: it changes the permission fields in the mysql.user table on disk to Y, and changes the access value of the user's corresponding object in the acl_user in memory to 'all 1'.

If a new client successfully logs in with the username ua, MySQL will maintain a thread object for the new connection. All judgments about global permissions are made directly using the permission bits stored inside the thread object.

The grant command updates the disk and the corresponding memory for global permissions. The newly created connection will use the new permissions. For the existing connection, its global permissions are not affected by the grant.
If you want to revoke the above permissions:

revoke all privileges on *.* from 'ua'@'%';

There are also two corresponding operations. The permission field in the disk is modified to bit N, and the access value of the object in memory is modified to bit 0.

mysqlDB permissions

grant all privileges on db1.* to 'ua'@'%' with grant option;

Use SELECT * FROM MYSQL.DB WHERE USER = 'UA'\G to view the current user's db permissions, as well as the permissions to modify objects on disk and in memory.

The db permissions are stored in the mysql.db table

Note: Unlike global permissions, db permissions will affect existing connection objects.

mysql table permissions and column permissions

Table permissions are stored in mysql.tables_priv, column permissions are stored in mysql.columns_priv, and these two types of permissions are combined and stored in the hash structure column_priv_hash in memory.

Similar to db permissions, each time these two permissions are granted, the data table will be modified and the hash structure in memory will be modified synchronously. Therefore, the operation of these two types of permissions will also affect existing connections.

Use scenarios for flush privileges

Some documents mention that the flush privileges command must be executed immediately after granting privileges for the grant statement to take effect. In fact, a more accurate statement should be that when the permissions in the data table are inconsistent with the permission data in the memory, the flush privileges statement can be used to rebuild the memory data to achieve a consistent state.

For example, if a record in a data table is deleted at a certain moment, but the data in the memory still exists, the authorization to the user fails because the record cannot be found in the data table.
At the same time, re-creating this user will not work, because when judging in memory, it will be considered that this user still exists.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.