×
Create a new article
Write your page title here:
We currently have 3,189 articles on s23. Type your article name above or create one of the articles listed here!



    s23
    3,189Articles

    Mysql-master-slave-replication

    mysql-master-slave-replication[edit]

    1)on the master and the slave a new "replication" user has to be added

    mysql> create user repuser@kurs007 identified by 'xxxxx';
    Query OK, 0 rows affected (0.00 sec)
    

    2) set some rights

    mysql> create user repuser@kurs007 identified by 'xxxxx';
    Query OK, 0 rows affected (0.00 sec)
    

    now we have to edit the my.ini on the master and the slave

    master:

    server_id=1
    

    (there are loads of options, it is also possible to encrypt the connection with ssl.)

    slave:

    server-id=2
    master-host=<ip_address or fqdn>
    master-port=3306
    master-user=repuer #username
    master-passwort=xxxxx #can be given here, or later, if mysqld is started
    master-connect-retry=60 #in seconds
    

    then we start the master, and check with the "show master status" command, which bin-log file is acutally used:

    mysql> show master status;
    +--------------------+----------+--------------+------------------+
    | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +--------------------+----------+--------------+------------------+
    | KURS006-bin.000006 |      106 | testdbsm     | mysql            |
    +--------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    
    mysql>
    

    then the slave has to be started and execute following command:

    change master to
    master_host='<hostname>',
    master_user='<repuser>',
    master_password='
    master_log_file='KURS006-bin.000006'
    <pre>
    
    test, if connection exists
    <pre>
    mysql> show processlist;
    +----+------+----------------+------+---------+------+-------+------------------+
    | Id | User | Host           | db   | Command | Time | State | Info             |
    +----+------+----------------+------+---------+------+-------+------------------+
    |  1 | root | localhost:1224 | NULL | Sleep   |    0 |       | NULL             |
    |  3 | root | localhost:1226 | NULL | Query   |    0 | NULL  | show processlist |
    +----+------+----------------+------+---------+------+-------+------------------+
    2 rows in set (0.00 sec)
    

    if it works you should see a funny messages like this:

    mysql> show processlist;
    | 12 | repuser | KURS007:1394   | NULL     | Binlog Dump |    9 | Has sent all binlog to slave; waiting for binlog to be updated
    
    Cookies help us deliver our services. By using our services, you agree to our use of cookies.
    Cookies help us deliver our services. By using our services, you agree to our use of cookies.