STEP01) Add this to /etc/my.cnf on ServerB
[mysqld]
log-bin=mysql-bin
STEP02) Restart mysql on ServerB without replication running
service mysql restart --skip-slave-start
STEP03) On ServerB, run this in mysql client
SHOW MASTER STATUS;
You should see something like this:
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 590591464 | | |
+------------------+-----------+--------------+------------------+
STEP04) Record the Log File and Position from STEP03
STEP05) Restart mysql on ServerB as normal
service mysql restart
STEP06) Setup Replication User on ServerB
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ...
STEP07) On ServerA, run the following
CHANGE MASTER TO
MASTER_HOST='IP Address of ServerB',
MASTER_PORT=3306,
MASTER_USER='replication username',
MASTER_PASSWORD='replication password',
MASTER_LOG_FILE='File From STEP04',
MASTER_LOG_POS=Position From STEP04;
Suppose ServerB's IP address is 10.1.2.30, replication user/pass is 'repluser'/'replpass'
CHANGE MASTER TO
MASTER_HOST='10.1.2.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=590591464;
STEP08) Start Replication on ServerA by running
START SLAVE;
STEP09) Run SHOW SLAVE STATUS\G on ServerA
It should look something like this:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.64.89.147
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000109
Read_Master_Log_Pos: 636515875
Relay_Log_File: relay-bin.000038
Relay_Log_Pos: 636509693
Relay_Master_Log_File: mysql-bin.000109
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 636515875
Relay_Log_Space: 636509885
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.01 sec)
If Slave_IO_Running and Slave_SQL_Running both say Yes, CONGRATULATIONS You've done it !!!
Give it a Try !!!
What concerns me fact that fieldC is not the lead column in the PRIMARY KEY.
What would be preferable is to reverse the order to the primary key columns
CREATE TABLE `my_table` (
`id` int(10) unsigned NOT NULL,
`fieldA` char(40) NOT NULL,
`fieldB` char(40) NOT NULL,
`fieldC` char(32) DEFAULT NULL,
-- some other fields
PRIMARY KEY (`fieldC`,`id`)
-- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
partition pNULLorEMPTY VALUES IN(NULL,''),
partition pValueA VALUES IN('valueA'),
partition pValueB VALUES IN('valueB'),
partition pValueC VALUES IN('valueC')
-- other partitions
);
This may help navigate faster through the partitions. The MySQL Query Optimizer may/may not be smart enough to need the reversal.
If you have other columns in the WHERE clauses such as
WHERE fieldC='...' AND fieldA='...'WHERE fieldC='...' AND fieldB='...'
you will need matching indexes so quickly search within one partition. Otherwise, you may find yourself doing a full table scan with the partition.
With that in mind, please create the table like this:
CREATE TABLE `my_table` (
`id` int(10) unsigned NOT NULL,
`fieldA` char(40) NOT NULL,
`fieldB` char(40) NOT NULL,
`fieldC` char(32) DEFAULT NULL,
-- some other fields
PRIMARY KEY (`fieldC`,`id`),
KEY C_A_Index (`fieldC`,`fieldA`),
KEY C_B_Index (`fieldC`,`fieldB`)
-- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
partition pNULLorEMPTY VALUES IN(NULL,''),
partition pValueA VALUES IN('valueA'),
partition pValueB VALUES IN('valueB'),
partition pValueC VALUES IN('valueC')
-- other partitions
);
This will then accommodate such WHERE clauses.
Give it a Try !!!
CAVEAT
I noticed your character fields are CHAR and not VARCHAR. That's great.
If any of the character fields are VARCHAR, please read on.
If there are any VARCHAR fields, you need not convert them manually. Simply change the row storage format as follows:
ALTER TABLE mytable ROW_FORMAT=Fixed;
This will make the table's read speed increase 20%-25%. I wrote about this before:
Best Answer
Sharding is for write scaling. And, yes, each shard has part of the data. (It might have part of the biggest table. Meanwhile, smaller tables might be manually kept in sync across the shards. Or you might have other servers for other things.)
Replication (Master(s) + Replica(s)) has a complete copy of the data on each server. One Master + one Replica provides some degree of HA, but the "failover" is likely to be manual.
Each shard could have a Replica for HA purposes. Or you could use a cluster (InnoDB Cluster or Galera) for each shard. That would give you a combination of read scaling, a little write scaling, and a lot of HA.
When using Master+Replica, all writes go to the Master. "Critical reads" need to go to the Master, too. Other reads can go to the Replica. Since you can have an arbitrary number of replicas, reads can scale "infinitely". (Booking.com had over 100 replicas a few years ago; I don't know how many today.)
A cluster has at least 3 nodes. It can be one Master and the rest as replicas, or it can be all Masters. With 3 (or more) they can vote on what to do if one machine goes dark. Then those in the majority continue running until you can fix the broken node.
Of course, if you put the Master and all its replicas (or all cluster nodes) in a single datacenter, you run the risk of a failure of all of them. (Yes, DCs do go down.)
PARTITIONingis rarely of any use. (I list 4 use cases here: http://mysql.rjweb.org/doc.php/partitionmaint )(Please switch from the term "Slave" to "Replica".)