But why? PK ID's shouldn't matter and if they do, you should be setting them explicitly and not through auto increment.Īlso, InnoDB uses B-Tree index for it's lookups, it pulls data from it's buffer cache first then fills any missing data from disk. Since you have a very small set of data you could maybe get away with it. This is generally considered bad practice though as it could result in PK collision which is. If you want to control where auto increment starts or is at currently on a table you can run:Īlter table yourTableName auto_increment=startingNumber The way the data is now stored on disk could be entirely different because of this operation. Yes the table has the same name and the same basic structure, but fundamentally is a different piece of data. First off when you run an alter on a table you're not actually modifying that table but creating an entirely new table with the modifications, the source table is locked, the data is copied and the old table is dropped. Not entirely sure what you're trying to accomplish here. That makes use of parallisation and writes into CSV files, which can be loaded quicker than SQL statements from mysqldump. In case you don't want a physical backup, but a logical backup similar to mysqldump, but faster you can use MySQL Shell's dump utility. )įor incremental backup and point in time recovery you can use the mysqlbinlog tool. (And of course this works without docker from any MySQL server. After running that script, when "initialization" is done it will stop ( MYSQL_INITIALIZE_ONLY=1) Note, that this will only work with Oracle/MySQL's mysql/mysql-server docker images in that way, as Docker, Inc.'s mysql doesn't have the initialize only option. With that script it will close from 127.0.0.1:3306 into the backup directory. This creates a temporary container, which will be removed after running ( -rm), which will tun that backup.sql file during the initialisation phase. v $PWD/backup.sql:/docker-entrypoint-initdb.d/backup.sql \ SET GLOBAL clone_valid_donor_list = "127.0.0.1:3306" ĬLONE INSTANCE FROM IDENTIFIED BY 'root' DATA DIRECTORY '/backup/' Īnd an directory backup in the current working directory you can do this: docker run \ What the hell is happening here? the same queries in MySQL 5 work as expected.Īssuming you have a recent MySQL the most simple physical backup is using the CLONE plugin.įor example given a file backup.sql like this: INSTALL PLUGIN clone SONAME 'mysql_clone.so' However the query select * from b returns all the records with their IDs ascending but the underlying records order is changed!Įxecute alter table to add auto increment column I expect table B to have its records in the same orders they were before the alter query but with a new auto increment column from 1 to N (number of records) The new table B has its records ordered as expected, no keys, no indexes, plain barbone table.Īlter table b add column id AUTO_INCREMENT PRIMARY KEY Given a table A I create new table B as subset of A and with a specific ordering:Įxample minimal working query, obviously a more involved select occurs.Ĭreate table b as select colA, colB from a order by colB I've migrated a database from MySQL 5.3 to 8 and an odd behavior emerged.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |