I tried something similar just now
Here is MySQL for My PC
mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------+------------------------------+
| VERSION_COMMENT | MySQL Community Server (GPL) |
| VERSION | 5.5.12-log |
| VERSION_COMPILE_MACHINE | x86 |
| DATADIR | C:\MySQL_5.5.12\data\ |
| VERSION_COMPILE_OS | Win64 |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)
I will run this using MyISAM
- Step 01) create a table called 'rolando'
- Step 02) insert 'dominique' and 'diamond'
- Step 03) copy the table structure to 'pamela'
- Step 04) alter 'pamela' to not have auto_increment
- Step 05) In DOS, copy rolando.MYD to pamela.MYD
- Step 06) run
REPAIR TABLE pamela;
(Rebuild pamela.MYI)
- Step 07) run
SELECT COUNT(1) FROM pamela;
- Step 08) run
SHOW CREATE TABLE pamela\G
- Step 09) run
SELECT * FROM pamela;
- Step 10) insert 'carlik' into pamela
- Step 11) run
SELECT * FROM pamela;
Let's see if these steps are kosher.
Here are Steps 1-4
mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)
mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)
mysql> create table rolando
-> (
-> name varchar(20),
-> id int not null auto_increment,
-> primary key (id)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from rolando;
+-----------+----+
| name | id |
+-----------+----+
| dominique | 1 |
| diamond | 2 |
+-----------+----+
2 rows in set (0.00 sec)
mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)
mysql> show create table rolando\G
*************************** 1. row ***************************
Table: rolando
Create Table: CREATE TABLE `rolando` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table pamela\G
*************************** 1. row ***************************
Table: pamela
Create Table: CREATE TABLE `pamela` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table pamela\G
*************************** 1. row ***************************
Table: pamela
Create Table: CREATE TABLE `pamela` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
mysql>
Here is Step 6
C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
1 file(s) copied.
C:\>
Here are the rest of the Steps starting at Step 7
mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning | Number of rows changed from 0 to 2 |
| test.pamela | repair | status | OK |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)
mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from pamela;
+-----------+----+
| name | id |
+-----------+----+
| dominique | 1 |
| diamond | 2 |
| carlik | 3 |
+-----------+----+
3 rows in set (0.00 sec)
mysql>
Dangerous game, isn't it ???
Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:
The technique we are about to demonstrate is unsupported,
undocumented, and may not work. Use it at your risk. We advise you to
back up you data first!
I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?
You got guts, @atxdba !!!
The traditional way would be to use pt-table-checksum and pt-table-sync
I like doing things a little different. I immediately run pt-table-sync
with the --sync-to-master --print
options.
Here is the --sync-to-master option
--sync-to-master
Treat the DSN as a slave and sync it to its master.
Treat the server you specified as a slave. Inspect SHOW SLAVE STATUS,
connect to the server’s master, and treat the master as the source and
the slave as the destination. Causes changes to be made on the master.
Sets --wait to 60 by default, sets --lock to 1 by default, and
disables --[no]transaction by default. See also --replicate, which
changes this option’s behavior.
Here is the --print option
--print
Print queries that will resolve differences.
If you don’t trust pt-table-sync, or just want to see what it will do, this is a good way to be safe. These queries are valid SQL and you can run them yourself if you want to sync the tables manually.
This, if you do something like this:
echo "SET SQL_LOG_BIN=0;" > /root/SQLChanges.sql
pt-table-sync --print --sync-to-master h=10.1.2.30,u=username,p=password >> /root/SQLChanges.sql
The file /root/SQLChanges.sql
will contain every change you need to execute on the Slave. Once you are satisfied with its contents, just execute the script on the Slave.
With regard to using LOAD DATA INFILE in Replication, @DTest answered this question about that. I further explained how mysql replicates LOAD DATA INFILE.
Best Answer
I think you will find an extra "statement" in the binlog that assigns the auto_inc value for the subsequent statement. This guarantees that the Slave has the same ids for SBR.
For RBR (Row-Based-Replication), there is no issue, since the id is part of the
INSERT
.In addition to the possibility of "burning" ("losing") ids, the ids can be replicated out of order. This because ids are grabbed as needed, but replication is in
COMMIT
-order. (This applies to SBR and RBR.)For multi-master,
auto_increment_increment > 1
-- another reason for gaps in the ids.