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



    s23
    3,187Articles

    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.

    Recent changes

  • Vaxima • 4 hours ago
  • Vaxima • 6 hours ago
  • Vaxima • 6 hours ago
  • Vaxima • 6 hours ago
  • Cookies help us deliver our services. By using our services, you agree to our use of cookies.