Mutiple MySql read/write credentials

[MrTheSoulz]

Hello, im trying to do some wierd setup where i want to write to 2 separate databases and read from local.
According to: https://laravel.com/docs/9.x/database#read-and-write-connections this should be possível but i seem unable to find docs on how to deal with different credentials.
Has someone delt with this before and can share some light?

Heres what i tried:

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'read' => [
                'host' => env('DB_HOST', '127.0.0.1'),
                'port' => env('DB_PORT', '3306'),
                'database' => env('DB_DATABASE', 'forge'),
                'username' => env('DB_USERNAME', 'forge'),
                'password' => env('DB_PASSWORD', ''),
            ],
            'write' => [
                'host' => [
                    env('DB_HOST', '127.0.0.1'),
                    'DOMAIN_HERE'
                ],
                'port' => [
                    env('DB_PORT', '3306'),
                    'PORT_HERE'
                ],
                'database' => [
                    env('DB_DATABASE', 'forge'),
                    'DATABASE_HERE'
                ],
                'username' => [
                    env('DB_USERNAME', 'forge'),
                    'USERNAME_HERE'
                ],
                'password' => [
                    env('DB_PASSWORD', ''),
                    'PASSWORD_HERE'
                ],
            ],
            'sticky' => true,
            //'host' => env('DB_HOST', '127.0.0.1'),
            //'port' => env('DB_PORT', '3306'),
            //'database' => env('DB_DATABASE', 'forge'),
            //'username' => env('DB_USERNAME', 'forge'),
            //'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

This results in a error:
[object] (ErrorException(code: 0): Array to string conversion at PATH/vendor/Laravel/framework/src/Illuminate/Database/Connectors/MySqlConnector.php:153)

[henzeb]

Your write config options should be string and not array with one item in it.

[MrTheSoulz]

According to Laravel doc's, you should be able to define multiple. Or i'm i understand it wrong?

Read & Write Connections

Sometimes you may wish to use one database connection for SELECT statements, and another for INSERT, UPDATE, and DELETE statements. Laravel makes this a breeze, and the proper connections will always be used whether you are using raw queries, the query builder, or the Eloquent ORM.

To see how read / write connections should be configured, let's look at this example:

'mysql' => [
    'read' => [
        'host' => [
            '192.168.1.1',
            '196.168.1.2',
        ],
    ],
    'write' => [
        'host' => [
            '196.168.1.3',
        ],
    ],
    'sticky' => true,
    'driver' => 'mysql',
    'database' => 'database',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

Note that three keys have been added to the configuration array: read, write and sticky. The read and write keys have array values containing a single key: host. The rest of the database options for the read and write connections will be merged from the main mysql configuration array.

You only need to place items in the read and write arrays if you wish to override the values from the main mysql array. So, in this case, 192.168.1.1 will be used as the host for the "read" connection, while 192.168.1.3 will be used for the "write" connection. The database credentials, prefix, character set, and all other options in the main mysql array will be shared across both connections. When multiple values exist in the host configuration array, a database host will be randomly chosen for each request.

[henzeb]

you can have multiple hosts. not multiple hosts with multiple credentials.

From what I understand, you must setup all your servers to have the same credentials. they are in a source/replica setup anyways.